﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using ExtendedControls;
using System.IO;
using AdoHelper;
using System.Threading;
using System.Text.RegularExpressions;
using HelperClasses;
using System.Runtime.Serialization.Formatters.Binary;
using System.Collections.Specialized;
using System.Collections;

namespace _3_tier_generator
{
    public partial class FormSelect : Form
    {
        private Exception error = null;
        private bool steadystate = false;
        private DataBase currentconnection = null;
        private DataTable tablesDataTable = null;
        private DataTable columnsDataTable = null;
        private DataTable constrainsDataTable = null;
        private DataTable SettingsDataTable = null;

        private string dataLayerPath;
        private string BusinessLayerPath;

        private string DataLayerProjectFiles = "";
        private string BusinessLayerProjectFiles = "";
        private string IdentifierPrefix = "[";
        private string IdentifierPostfix = "]";
        private DataView foundRows = new DataView();

        public FormSelect()
        {
            InitializeComponent();
            FormResizing a = new FormResizing(this);
            this.comboBoxTrueOrFalse.SelectedIndex = 0;
        }

        #region formevents
        //-------------------------------------------------------------------------------------------------       
        private void buttonConnect_Click(object sender, EventArgs e)
        {
            this.steadystate = false;
            DataBase masterDataBase = null;
            if (this.textBoxUserID.Text.Trim() != "")
            {
                SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder { DataSource = this.textBoxServer.Text, InitialCatalog = "Master", UserID = this.textBoxUserID.Text, Password = this.textBoxPassword.Text };
                masterDataBase = new DataBase(scsb.ConnectionString);
            }
            else
            {
                SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder { DataSource = this.textBoxServer.Text, InitialCatalog = "Master", IntegratedSecurity = true };
                masterDataBase = new DataBase(scsb.ConnectionString);
            }
            string Query = "SELECT     database_id, name FROM         sys.databases";
            DataTable databasesTable = masterDataBase.GetDataTable(Query, CommandType.Text, ref this.error);
            if (error == null)
            {
                this.comboBoxDatabase.DataSource = databasesTable;
                this.comboBoxDatabase.DisplayMember = "name";
                this.comboBoxDatabase.ValueMember = "database_id";
                this.comboBoxDatabase.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
                this.comboBoxDatabase.AutoCompleteSource = AutoCompleteSource.ListItems;
                this.steadystate = true;
                this.getDataBaseInformation();
            }
            else
            {
                MessageBox.Show(error.Message);
            }
        }
        //-------------------------------------------------------------------------------------------------       
        private void buttonExtract_Click(object sender, EventArgs e)
        {
            if (this.folderBrowserDialog.ShowDialog() == DialogResult.OK)
            {
                Thread t = new Thread(this.extract);
                t.IsBackground = true;
                t.Start();
            }
        }
        //-------------------------------------------------------------------------------------------------       
        private void checkBoxCheckAll_CheckedChanged(object sender, EventArgs e)
        {
            for (int i = 0; i < this.tablesDataTable.Rows.Count; i++)
            {
                this.tablesDataTable.Rows[i]["Choose"] = this.checkBoxCheckAll.Checked;
            }
        }
        //-------------------------------------------------------------------------------------------------
        private void buttonUseGetDate_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < this.extendedDataGridViewSettings.SelectedRows.Count; i++)
            {
                this.extendedDataGridViewSettings.SelectedRows[i].Cells["UseGetDate"].Value = this.comboBoxTrueOrFalse.SelectedIndex;
            }
        }
        //-------------------------------------------------------------------------------------------------
        private void buttonSaveSettings_Click(object sender, EventArgs e)
        {
            if (this.saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                this.SettingsDataTable.AcceptChanges();
                this.SettingsDataTable.TableName = "SettingsDataTable";
                this.SettingsDataTable.WriteXml(this.saveFileDialog1.FileName, XmlWriteMode.WriteSchema);
            }
        }
        //-------------------------------------------------------------------------------------------------
        private void buttonLoadSettings_Click(object sender, EventArgs e)
        {
            if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                DataTable dt = new DataTable();
                try
                {
                    dt.ReadXml(this.openFileDialog1.FileName);
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        string filter = string.Format("TABLE_SCHEMA='{0}' AND TABLE_NAME='{1}' AND COLUMN_NAME='{2}'", dt.Rows[i]["TABLE_SCHEMA"], dt.Rows[i]["TABLE_NAME"], dt.Rows[i]["COLUMN_NAME"]);
                        DataRow[] foundRows = this.SettingsDataTable.Select(filter);
                        if (foundRows.Length > 0)
                        {
                            foundRows[0]["UseGetDate"] = dt.Rows[i]["UseGetDate"];
                        }
                    }
                }
                catch
                {
                }
            }
        }
        //-------------------------------------------------------------------------------------------------
        private void comboBoxDatabase_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.getDataBaseInformation();
        }
        //-------------------------------------------------------------------------------------------------
        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            this.getDataBaseInformation();
        }
        //-------------------------------------------------------------------------------------------------
        private void textBoxIdentifierStart_TextChanged(object sender, EventArgs e)
        {
            this.textBoxsqlSuffix.Text = this.textBoxsqlSuffix.Text.Replace(IdentifierPrefix, this.textBoxIdentifierPrefix.Text);
            IdentifierPrefix = this.textBoxIdentifierPrefix.Text;
        }
        //-------------------------------------------------------------------------------------------------
        private void textBoxIdentifierEnd_TextChanged(object sender, EventArgs e)
        {
            this.textBoxsqlSuffix.Text = this.textBoxsqlSuffix.Text.Replace(IdentifierPostfix, this.textBoxIdentifierPostfix.Text);
            IdentifierPostfix = this.textBoxIdentifierPostfix.Text;
        }
        //-------------------------------------------------------------------------------------------------
        #endregion

        #region database
        //-------------------------------------------------------------------------------------------------       
        private void generateInsertProcedure(string Schema, string tableName)
        {
            string filter = string.Format("TABLE_SCHEMA='{0}' AND TABLE_NAME='{1}'", AdoHelper.DataBase.EscapeSqlCharacter(Schema, false), AdoHelper.DataBase.EscapeSqlCharacter(tableName, false));
            DataView foundRows = new DataView(this.columnsDataTable, filter, "ORDINAL_POSITION", DataViewRowState.CurrentRows);


            string CurrentDate = DateTime.Now.ToString("[[yyyyMMddHHmmss]]");
            string Author = "Ahmed Fathi Hamouda Abd El Hafez";
            string dataBaseName = this.tablesDataTable.Rows[0]["TABLE_CATALOG"].ToString();
            string tableFullName = this.textBoxIdentifierPrefix.Text + Schema + this.textBoxIdentifierPostfix.Text + "." + this.textBoxIdentifierPrefix.Text + tableName + this.textBoxIdentifierPostfix.Text;
            string ProcedureName = Regex.Replace(this.textBoxsqlSuffix.Text, "#Event#", "Insert", RegexOptions.IgnoreCase);
            ProcedureName = Regex.Replace(ProcedureName, "#TableName#", tableName, RegexOptions.IgnoreCase);
            string parametersDefinition = "";
            string ColumnsConcatenated = "";
            string ParametersConcatenated = "";
            //string DateParameters = "";
            //string identityExpression = "";
            string selectOutputParameters = "";
            string selectWhereConditions = "";
            string selectStatement = "";


            for (int i = 0; i < foundRows.Count; i++)
            {
                string columnName = this.textBoxIdentifierPrefix.Text + foundRows[i]["COLUMN_NAME"].ToString() + this.textBoxIdentifierPostfix.Text;
                string ParameterName = this.textBoxParameterPrefix.Text + Regex.Replace(foundRows[i]["COLUMN_NAME"].ToString(), "\\W", "_");

                //get column dataType 
                string dataType = foundRows[i]["DATA_TYPE"].ToString();
                if (dataType.ToLower() == "varchar" || dataType.ToLower() == "nvarchar")
                {
                    string characterLength = foundRows[i]["CHARACTER_MAXIMUM_LENGTH"].ToString();
                    if (characterLength == "-1")
                    {
                        characterLength = "max";
                    }
                    dataType += string.Format("({0})", characterLength);
                }
                else if (dataType.ToLower() == "decimal" || dataType.ToLower() == "numeric")
                {
                    string numericPercision = foundRows[i]["NUMERIC_PRECISION"].ToString();
                    string numericScale = foundRows[i]["NUMERIC_SCALE"].ToString();
                    dataType += string.Format("({0},{1}) ", numericPercision, numericScale);
                }

                string parameterDirection = "";
                //get column deafultvalue 
                string columnDeafultvalue = foundRows[i]["COLUMN_DEFAULT"].ToString();
                if (columnDeafultvalue != "")
                {
                    if (columnDeafultvalue.StartsWith("(("))
                    {
                        while (columnDeafultvalue.StartsWith("(") && columnDeafultvalue.EndsWith(")"))
                        {
                            columnDeafultvalue = columnDeafultvalue.Substring(1, columnDeafultvalue.Length - 2);
                        }
                    }
                    else
                    {
                        columnDeafultvalue = "Null";
                    }
                }
                else
                {
                    columnDeafultvalue = "Null";
                }




                //identity column
                bool isIdentity = (foundRows[i]["IS_IDENTITY"].ToString() == "1");
                bool isComputed = (foundRows[i]["IS_COMPUTED"].ToString() == "1");
                bool UseGetDateInInsert = Regex.IsMatch(foundRows[i]["COLUMN_DEFAULT"].ToString(), "getdate", RegexOptions.IgnoreCase);
                bool isTimeStamp = dataType.ToLower().Contains("timestamp");
                if (isIdentity || isComputed)
                {
                    //identityExpression = string.Format("SET {0}=SCOPE_IDENTITY()", ParameterName);
                    parameterDirection = "OUTPUT";
                    if(selectOutputParameters!="")
                    {
                        selectOutputParameters+=", ";
                    }
                    selectOutputParameters += ParameterName + " = " + columnName;
                }
                else if (UseGetDateInInsert)
                {
                    //DateParameters += string.Format("\r\n	SET {0}=getdate()", ParameterName);
                    if (ColumnsConcatenated != "")
                    {
                        ColumnsConcatenated += ", ";
                        ParametersConcatenated += ", ";
                    }
                    ColumnsConcatenated += columnName;
                    ParametersConcatenated += this.textBoxSystemDateFunction.Text; 
                    parameterDirection = "OUTPUT";
                    if (selectOutputParameters != "")
                    {
                        selectOutputParameters += ", ";
                    }
                    selectOutputParameters += ParameterName + " = " + columnName;
                }
                else if (isTimeStamp)
                {
                    parameterDirection = "OUTPUT";
                    if (selectOutputParameters != "")
                    {
                        selectOutputParameters += ", ";
                    }
                    selectOutputParameters += ParameterName + " = " + columnName;
                }
                else 
                {
                    if (ColumnsConcatenated != "")
                    {
                        ColumnsConcatenated += ", ";
                        ParametersConcatenated += ", ";
                    }
                    ColumnsConcatenated += columnName;
                    ParametersConcatenated += ParameterName;
                    if (selectWhereConditions != "")
                    {
                        selectWhereConditions += "\r\n	AND ";
                    }
                    selectWhereConditions += string.Format(" ( {0} ={1} )", columnName, ParameterName);
                }

                //parameters definition
                if (parametersDefinition != "")
                {
                    parametersDefinition += " , ";
                }
                parametersDefinition += string.Format("\r\n	{0} {1} ={2} {3}", ParameterName, dataType,columnDeafultvalue, parameterDirection);
            }

            if (selectOutputParameters != "")
            {
                selectStatement += "SELECT " + selectOutputParameters
                    + "\r\n" + "    FROM " + tableFullName
                    + "\r\n" + "    WHERE " + selectWhereConditions;
            }

            string AlterTemplate = @"/*IF EXISTS (SELECT * FROM dbo.Sysobjects where id = object_id(N'_ProcedureName_') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   drop procedure _ProcedureName_*/";
            AlterTemplate = Regex.Replace(AlterTemplate, "_ProcedureName_", ProcedureName, RegexOptions.IgnoreCase);
            if (this.checkBoxUseAlter.Checked)
            {
                AlterTemplate = AlterTemplate.Replace("/*", "").Replace("*/", "");
            }

            string Template = File.ReadAllText(Application.StartupPath + "\\Templates\\Sql Procedures\\InsertProcedure.sql");
            Template = Regex.Replace(Template, "_Date_", CurrentDate, RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_Author_", Author, RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_DataBaseName_", dataBaseName, RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_TableFullName_", tableFullName, RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_ProcedureName_", ProcedureName, RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_AlterTemplate_", AlterTemplate.Trim(), RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_ParametersDefinition_", parametersDefinition.Trim(), RegexOptions.IgnoreCase);
            //Template = Regex.Replace(Template, "_DateParameters_", DateParameters.Trim(), RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_ColumnsConcatenated_", ColumnsConcatenated.Trim(), RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_ParametersConcatenated_", ParametersConcatenated.Trim(), RegexOptions.IgnoreCase);
            //Template = Regex.Replace(Template, "_IdentityExpression_", identityExpression.Trim(), RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_selectStatement_", selectStatement.Trim(), RegexOptions.IgnoreCase);
            
            //File.WriteAllText(this.folderBrowserDialog.SelectedPath + "\\SQL\\Insert\\" + ProcedureName.Replace("[", "").Replace("]", "") + ".sql", sb.ToString());
            File.AppendAllText(this.dataLayerPath + "\\SQL\\Tables\\" + Schema + "\\" + tableName + ".sql", Template);
            File.AppendAllText(this.dataLayerPath + "\\SQL\\ALL.sql", "\r\n\r\n\r\n--" + Template);
        }
        //-------------------------------------------------------------------------------------------------       
        private void generateUpdateProcedure(string Schema, string tableName)
        {
            string filter = string.Format("TABLE_SCHEMA='{0}' AND TABLE_NAME='{1}'", DataBase.EscapeSqlCharacter(Schema, false), DataBase.EscapeSqlCharacter(tableName, false));
            DataView foundRows = new DataView(this.columnsDataTable, filter, "ORDINAL_POSITION", DataViewRowState.CurrentRows);


            string CurrentDate = DateTime.Now.ToString("[[yyyyMMddHHmmss]]");
            string Author = "Ahmed Fathi Hamouda Abd El Hafez";
            string dataBaseName = this.tablesDataTable.Rows[0]["TABLE_CATALOG"].ToString();
            string tableFullName = this.textBoxIdentifierPrefix.Text + Schema + this.textBoxIdentifierPostfix.Text + "." + this.textBoxIdentifierPrefix.Text + tableName + this.textBoxIdentifierPostfix.Text;
            string ProcedureName = Regex.Replace(this.textBoxsqlSuffix.Text, "#Event#", "update", RegexOptions.IgnoreCase);
            ProcedureName = Regex.Replace(ProcedureName, "#TableName#", tableName, RegexOptions.IgnoreCase);
            string parametersDefinition = "";
            string UpdatedColumns = "";
            string PrimaryIdentifiersWhereCondition = "";
            //string DateParameters = "";
            string timeStampParameter = "";
            string timeStampColumn = "";
            string timeStampCheck = "";
            string endTimeStampCheck = "";
            string selectOutputParameters = "";
            string selectStatement = "";

            for (int i = 0; i < foundRows.Count; i++)
            {
                string columnName = this.textBoxIdentifierPrefix.Text + foundRows[i]["COLUMN_NAME"].ToString() + this.textBoxIdentifierPostfix.Text;
                string ParameterName = this.textBoxParameterPrefix.Text + Regex.Replace(foundRows[i]["COLUMN_NAME"].ToString(), "\\W", "_");

                //get column dataType 
                string dataType = foundRows[i]["DATA_TYPE"].ToString();
                if (dataType.ToLower() == "varchar" || dataType.ToLower() == "nvarchar")
                {
                    string characterLength = foundRows[i]["CHARACTER_MAXIMUM_LENGTH"].ToString();
                    if (characterLength == "-1")
                    {
                        characterLength = "max";
                    }
                    dataType += string.Format("({0})", characterLength);
                }
                else if (dataType.ToLower() == "decimal" || dataType.ToLower() == "numeric")
                {
                    string numericPercision = foundRows[i]["NUMERIC_PRECISION"].ToString();
                    string numericScale = foundRows[i]["NUMERIC_SCALE"].ToString();
                    dataType += string.Format("({0},{1}) ", numericPercision, numericScale);
                }



                string parameterDirection = "";
                //get column deafultvalue 
                string columnDeafultvalue = foundRows[i]["COLUMN_DEFAULT"].ToString();
                bool useGetDateInInsert = Regex.IsMatch(columnDeafultvalue, "getdate", RegexOptions.IgnoreCase);
                if (columnDeafultvalue != "")
                {
                    if (columnDeafultvalue.StartsWith("(("))
                    {
                        while (columnDeafultvalue.StartsWith("(") && columnDeafultvalue.EndsWith(")"))
                        {
                            columnDeafultvalue = columnDeafultvalue.Substring(1, columnDeafultvalue.Length - 2);
                        }
                    }
                    else
                    {
                        columnDeafultvalue = "Null";
                    }
                }
                else
                {
                    columnDeafultvalue = "Null";
                }

                string primaryIdentifierfilter = string.Format("TABLE_SCHEMA='{0}' AND TABLE_NAME='{1}' AND COLUMN_NAME='{2}' ", DataBase.EscapeSqlCharacter(Schema, false), DataBase.EscapeSqlCharacter(tableName, false), DataBase.EscapeSqlCharacter(foundRows[i]["COLUMN_NAME"].ToString(), false));
                DataView dv = new DataView(this.constrainsDataTable, primaryIdentifierfilter, "ORDINAL_POSITION", DataViewRowState.CurrentRows);

                string settingfilter = string.Format("TABLE_SCHEMA='{0}' AND TABLE_NAME='{1}' AND COLUMN_NAME='{2}'", DataBase.EscapeSqlCharacter(Schema, false), DataBase.EscapeSqlCharacter(tableName, false), DataBase.EscapeSqlCharacter(foundRows[i]["COLUMN_NAME"].ToString(), false));
                DataRow[] settingfoundRows = this.SettingsDataTable.Select(settingfilter);

                bool isPrimaryKey = (dv.Count > 0);
                bool isIdentity = (foundRows[i]["IS_IDENTITY"].ToString() == "1");
                bool isComputed = (foundRows[i]["IS_COMPUTED"].ToString() == "1");
                bool UseGetDateInInsert = Regex.IsMatch(foundRows[i]["COLUMN_DEFAULT"].ToString(), "getdate", RegexOptions.IgnoreCase);
                bool UseGetDateInUpdate = (settingfoundRows.Length > 0 && Convert.ToBoolean(settingfoundRows[0]["UseGetDate"]));
                bool isTimeStamp = dataType.ToLower().Contains("timestamp");

                if (isIdentity || isComputed)
                {
                    //identityExpression = string.Format("SET {0}=SCOPE_IDENTITY()", ParameterName);
                    parameterDirection = "OUTPUT";
                    if (selectOutputParameters != "")
                    {
                        selectOutputParameters += ", ";
                    }
                    selectOutputParameters += ParameterName + " = " + columnName;
                }
                else if (UseGetDateInInsert)
                {
                    //DateParameters += string.Format("\r\n	SET {0}=getdate()", ParameterName);
                    parameterDirection = "OUTPUT";
                    if (selectOutputParameters != "")
                    {
                        selectOutputParameters += ", ";
                    }
                    selectOutputParameters += ParameterName + " = " + columnName;
                }
                else if (UseGetDateInUpdate)
                {
                    parameterDirection = "OUTPUT";
                    if (selectOutputParameters != "")
                    {
                        selectOutputParameters += ", ";
                    }
                    selectOutputParameters += ParameterName + " = " + columnName;
                    if (UpdatedColumns != "")
                    {
                        UpdatedColumns += ", ";
                    }
                    UpdatedColumns += "\r\n	" + columnName + " =" + this.textBoxSystemDateFunction.Text;
                }
                else if (isTimeStamp)
                {
                    parameterDirection = "OUTPUT";
                    if (selectOutputParameters != "")
                    {
                        selectOutputParameters += ", ";
                    }
                    selectOutputParameters += ParameterName + " = " + columnName;
                    timeStampParameter = ParameterName;
                    timeStampColumn = columnName;
                }
                else
                {
                    if (UpdatedColumns != "")
                    {
                        UpdatedColumns += ", ";
                    }
                    UpdatedColumns += "\r\n		" + columnName + " =" + ParameterName;
                }
                if (isPrimaryKey)
                {
                    if (PrimaryIdentifiersWhereCondition != "")
                    {
                        PrimaryIdentifiersWhereCondition += "\r\n	AND ";
                    }
                    else
                    {
                        PrimaryIdentifiersWhereCondition = "	WHERE ";
                    }
                    PrimaryIdentifiersWhereCondition += string.Format("({0} = {1})", columnName, ParameterName);
                }

                //parameters definition
                if (parametersDefinition != "")
                {
                    parametersDefinition += " , ";
                }
                parametersDefinition += string.Format("\r\n	{0} {1} ={2} {3}", ParameterName, dataType, columnDeafultvalue, parameterDirection);
            }

            if (timeStampParameter != "")
            {
                if (parametersDefinition != "")
                {
                    parametersDefinition += " , ";
                }
                parametersDefinition += "\r\n	" + this.textBoxParameterPrefix.Text + "OldTimeStamp timeStamp OUTPUT";
                timeStampCheck += "    SELECT " + this.textBoxParameterPrefix.Text + "OldTimeStamp = " + timeStampColumn;
                timeStampCheck += "\r\n    FROM " + tableFullName;
                timeStampCheck += "\r\n    " + PrimaryIdentifiersWhereCondition;
                timeStampCheck += "\r\n\r\n    IF  " + this.textBoxParameterPrefix.Text + "OldTimeStamp = " + timeStampParameter; ;
                timeStampCheck += "\r\n    BEGIN";
                endTimeStampCheck += "\r\n    END";
            }
            if (selectOutputParameters != "")
            {
                selectStatement += "SELECT " + selectOutputParameters
                    + "\r\n" + "    FROM " + tableFullName
                    + "\r\n" + PrimaryIdentifiersWhereCondition;
            }



            string AlterTemplate = @"/*IF EXISTS (SELECT * FROM dbo.Sysobjects where id = object_id(N'_ProcedureName_') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   drop procedure _ProcedureName_*/";
            AlterTemplate = Regex.Replace(AlterTemplate, "_ProcedureName_", ProcedureName, RegexOptions.IgnoreCase);
            if (this.checkBoxUseAlter.Checked)
            {
                AlterTemplate = AlterTemplate.Replace("/*", "").Replace("*/", "");
            }

            string Template = File.ReadAllText(Application.StartupPath + "\\Templates\\Sql Procedures\\UpdateProcedure.sql");
            Template = Regex.Replace(Template, "_Date_", CurrentDate, RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_Author_", Author, RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_DataBaseName_", dataBaseName, RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_TableFullName_", tableFullName, RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_ProcedureName_", ProcedureName, RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_AlterTemplate_", AlterTemplate.Trim(), RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_ParametersDefinition_", parametersDefinition.Trim(), RegexOptions.IgnoreCase);
            //Template = Regex.Replace(Template, "_DateParameters_", DateParameters.Trim(), RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_UpdatedColumns_", UpdatedColumns.Trim(), RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_PrimaryIdentifiersWhereCondition_", PrimaryIdentifiersWhereCondition.Trim(), RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_SelectStatement_", selectStatement.Trim(), RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_TimeStampCheck_", timeStampCheck.Trim(), RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_EndtimeStampCheck_", endTimeStampCheck.Trim(), RegexOptions.IgnoreCase);

            //File.WriteAllText(this.folderBrowserDialog.SelectedPath + "\\SQL\\Insert\\" + ProcedureName.Replace("[", "").Replace("]", "") + ".sql", sb.ToString());
            File.AppendAllText(dataLayerPath + "\\SQL\\Tables\\" + Schema + "\\" + tableName + ".sql", Template);
            File.AppendAllText(dataLayerPath + "\\SQL\\ALL.sql", "\r\n\r\n\r\n--" + Template);

        }
        //-------------------------------------------------------------------------------------------------       
        private void generateDeleteProcedure(string Schema, string tableName)
        {
            string filter = string.Format("TABLE_SCHEMA='{0}' AND TABLE_NAME='{1}'", DataBase.EscapeSqlCharacter(Schema, false), DataBase.EscapeSqlCharacter(tableName, false));
            DataView foundRows = new DataView(this.columnsDataTable, filter, "ORDINAL_POSITION", DataViewRowState.CurrentRows);

            string CurrentDate = DateTime.Now.ToString("[[yyyyMMddHHmmss]]");
            string Author = "Ahmed Fathi Hamouda Abd El Hafez";
            string dataBaseName = this.tablesDataTable.Rows[0]["TABLE_CATALOG"].ToString();
            string tableFullName = this.textBoxIdentifierPrefix.Text + Schema + this.textBoxIdentifierPostfix.Text + "." + this.textBoxIdentifierPrefix.Text + tableName + this.textBoxIdentifierPostfix.Text;
            string ProcedureName = Regex.Replace(this.textBoxsqlSuffix.Text, "#Event#", "Delete", RegexOptions.IgnoreCase);
            ProcedureName = Regex.Replace(ProcedureName, "#TableName#", tableName, RegexOptions.IgnoreCase);
            string parametersDefinition = "";
            string WhereCondition = "";

            for (int i = 0; i < foundRows.Count; i++)
            {
                string columnName = this.textBoxIdentifierPrefix.Text + foundRows[i]["COLUMN_NAME"].ToString() + this.textBoxIdentifierPostfix.Text;
                string ParameterName = this.textBoxParameterPrefix.Text + Regex.Replace(foundRows[i]["COLUMN_NAME"].ToString(), "\\W", "_");

                //get column dataType 
                string dataType = foundRows[i]["DATA_TYPE"].ToString();
                if (dataType.ToLower() == "varchar" || dataType.ToLower() == "nvarchar")
                {
                    string characterLength = foundRows[i]["CHARACTER_MAXIMUM_LENGTH"].ToString();
                    if (characterLength == "-1")
                    {
                        characterLength = "max";
                    }
                    dataType += string.Format("({0})", characterLength);
                }
                else if (dataType.ToLower() == "decimal" || dataType.ToLower() == "numeric")
                {
                    string numericPercision = foundRows[i]["NUMERIC_PRECISION"].ToString();
                    string numericScale = foundRows[i]["NUMERIC_SCALE"].ToString();
                    dataType += string.Format("({0},{1}) ", numericPercision, numericScale);
                }

                // where conditions
                if (WhereCondition != "")
                {
                    WhereCondition += "\r\n	AND ";
                }

                if (dataType.ToLower() == "text" || dataType.ToLower() == "ntext" || dataType.ToLower().StartsWith("varchar") || dataType.ToLower().StartsWith("nvarchar"))
                {
                    WhereCondition += string.Format("( {0} like ISNULL({1}, {0}) )", columnName, ParameterName);
                }
                else
                {
                    WhereCondition += string.Format("( {0} = ISNULL({1}, {0}) )", columnName, ParameterName);
                }


                //parameters definition
                if (parametersDefinition != "")
                {
                    parametersDefinition += " , ";
                }
                parametersDefinition += string.Format("\r\n	{0} {1} =null ", ParameterName, dataType);
            }

            string AlterTemplate = @"/*IF EXISTS (SELECT * FROM dbo.Sysobjects where id = object_id(N'_ProcedureName_') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   drop procedure _ProcedureName_*/";
            AlterTemplate = Regex.Replace(AlterTemplate, "_ProcedureName_", ProcedureName, RegexOptions.IgnoreCase);
            if (this.checkBoxUseAlter.Checked)
            {
                AlterTemplate = AlterTemplate.Replace("/*", "").Replace("*/", "");
            }

            string Template = File.ReadAllText(Application.StartupPath + "\\Templates\\Sql Procedures\\DeleteProcedure.sql");
            Template = Regex.Replace(Template, "_Date_", CurrentDate, RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_Author_", Author, RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_DataBaseName_", dataBaseName, RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_TableFullName_", tableFullName, RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_ProcedureName_", ProcedureName, RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_AlterTemplate_", AlterTemplate.Trim(), RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_ParametersDefinition_", parametersDefinition.Trim(), RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_WhereCondition_", WhereCondition.Trim(), RegexOptions.IgnoreCase);

            //File.WriteAllText(this.folderBrowserDialog.SelectedPath + "\\SQL\\Insert\\" + ProcedureName.Replace("[", "").Replace("]", "") + ".sql", sb.ToString());
            File.AppendAllText(this.dataLayerPath + "\\SQL\\Tables\\" + Schema + "\\" + tableName + ".sql", Template);
            File.AppendAllText(this.dataLayerPath + "\\SQL\\ALL.sql", "\r\n\r\n\r\n--" + Template);
        }
        //-------------------------------------------------------------------------------------------------       
        private void generateSelectProcedure(string Schema, string tableName)
        {
            string filter = string.Format("TABLE_SCHEMA='{0}' AND TABLE_NAME='{1}'", DataBase.EscapeSqlCharacter(Schema, false), DataBase.EscapeSqlCharacter(tableName, false));
            DataView foundRows = new DataView(this.columnsDataTable, filter, "ORDINAL_POSITION", DataViewRowState.CurrentRows);

            string CurrentDate = DateTime.Now.ToString("[[yyyyMMddHHmmss]]");
            string Author = "Ahmed Fathi Hamouda Abd El Hafez";
            string dataBaseName = this.tablesDataTable.Rows[0]["TABLE_CATALOG"].ToString();
            string tableFullName = this.textBoxIdentifierPrefix.Text + Schema + this.textBoxIdentifierPostfix.Text + "." + this.textBoxIdentifierPrefix.Text + tableName + this.textBoxIdentifierPostfix.Text;
            string ProcedureName = Regex.Replace(this.textBoxsqlSuffix.Text, "#Event#", "Select", RegexOptions.IgnoreCase);
            ProcedureName = Regex.Replace(ProcedureName, "#TableName#", tableName, RegexOptions.IgnoreCase);
            string parametersDefinition = "";
            string WhereCondition = "";

            for (int i = 0; i < foundRows.Count; i++)
            {
                string columnName = this.textBoxIdentifierPrefix.Text + foundRows[i]["COLUMN_NAME"].ToString() + this.textBoxIdentifierPostfix.Text;
                string ParameterName = this.textBoxParameterPrefix.Text + Regex.Replace(foundRows[i]["COLUMN_NAME"].ToString(), "\\W", "_");

                //get column dataType 
                string dataType = foundRows[i]["DATA_TYPE"].ToString();
                if (dataType.ToLower() == "varchar" || dataType.ToLower() == "nvarchar")
                {
                    string characterLength = foundRows[i]["CHARACTER_MAXIMUM_LENGTH"].ToString();
                    if (characterLength == "-1")
                    {
                        characterLength = "max";
                    }
                    dataType += string.Format("({0})", characterLength);
                }
                else if (dataType.ToLower() == "decimal" || dataType.ToLower() == "numeric")
                {
                    string numericPercision = foundRows[i]["NUMERIC_PRECISION"].ToString();
                    string numericScale = foundRows[i]["NUMERIC_SCALE"].ToString();
                    dataType += string.Format("({0},{1}) ", numericPercision, numericScale);
                }

                // where conditions
                if (WhereCondition != "")
                {
                    WhereCondition += "\r\n	AND ";
                }

                if (dataType.ToLower() == "text" || dataType.ToLower() == "ntext" || dataType.ToLower().StartsWith("varchar") || dataType.ToLower().StartsWith("nvarchar"))
                {
                    WhereCondition += string.Format("( {0} like ISNULL({1}, {0}) )", columnName, ParameterName);
                }
                else
                {
                    WhereCondition += string.Format("( {0} = ISNULL({1}, {0}) )", columnName, ParameterName);
                }


                //parameters definition
                if (parametersDefinition != "")
                {
                    parametersDefinition += " , ";
                }
                parametersDefinition += string.Format("\r\n	{0} {1} =null ", ParameterName, dataType);
            }

            string AlterTemplate = @"/*IF EXISTS (SELECT * FROM dbo.Sysobjects where id = object_id(N'_ProcedureName_') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   drop procedure _ProcedureName_*/";
            AlterTemplate = Regex.Replace(AlterTemplate, "_ProcedureName_", ProcedureName, RegexOptions.IgnoreCase);
            if (this.checkBoxUseAlter.Checked)
            {
                AlterTemplate = AlterTemplate.Replace("/*", "").Replace("*/", "");
            }

            string Template = File.ReadAllText(Application.StartupPath + "\\Templates\\Sql Procedures\\SelectProcedure.sql");
            Template = Regex.Replace(Template, "_Date_", CurrentDate, RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_Author_", Author, RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_DataBaseName_", dataBaseName, RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_TableFullName_", tableFullName, RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_ProcedureName_", ProcedureName, RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_AlterTemplate_", AlterTemplate.Trim(), RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_ParametersDefinition_", parametersDefinition.Trim(), RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_WhereCondition_", WhereCondition.Trim(), RegexOptions.IgnoreCase);

            //File.WriteAllText(this.folderBrowserDialog.SelectedPath + "\\SQL\\Insert\\" + ProcedureName.Replace("[", "").Replace("]", "") + ".sql", sb.ToString());
            File.AppendAllText(this.dataLayerPath + "\\SQL\\Tables\\" + Schema + "\\" + tableName + ".sql", Template);
            File.AppendAllText(this.dataLayerPath + "\\SQL\\ALL.sql", "\r\n\r\n\r\n--" + Template);
        }
        //-------------------------------------------------------------------------------------------------       
        private void generateInsertUpdateProcedure(string Schema, string tableName)
        {
            string filter = string.Format("TABLE_SCHEMA='{0}' AND TABLE_NAME='{1}'", DataBase.EscapeSqlCharacter(Schema, false), DataBase.EscapeSqlCharacter(tableName, false));
            DataView foundRows = new DataView(this.columnsDataTable, filter, "ORDINAL_POSITION", DataViewRowState.CurrentRows);


            string CurrentDate = DateTime.Now.ToString("[[yyyyMMddHHmmss]]");
            string Author = "Ahmed Fathi Hamouda Abd El Hafez";
            string dataBaseName = this.tablesDataTable.Rows[0]["TABLE_CATALOG"].ToString();
            string tableFullName = this.textBoxIdentifierPrefix.Text + Schema + this.textBoxIdentifierPostfix.Text + "." + this.textBoxIdentifierPrefix.Text + tableName + this.textBoxIdentifierPostfix.Text;
            string ProcedureName = Regex.Replace(this.textBoxsqlSuffix.Text, "#Event#", "Insert-Update", RegexOptions.IgnoreCase);
            ProcedureName = Regex.Replace(ProcedureName, "#TableName#", tableName, RegexOptions.IgnoreCase);
            string parametersDefinition = "";
            string ColumnsConcatenated = "";
            string ParametersConcatenated = "";
            //string DateParametersInsert = "";
            //string DateParametersUpdate = "";
            //string identityExpressionInsert = "";
            //string identityExpressionUpdate = "";
            string UpdatedColumns = "";
            string PrimaryIdentifiersWhereCondition = "";
            string timeStampParameter = "";
            string timeStampColumn = "";
            string timeStampCheck = "";
            string endTimeStampCheck = "";
            string selectOutputParameters = "";
            string selectStatement = "";


            for (int i = 0; i < foundRows.Count; i++)
            {
                string columnName = this.textBoxIdentifierPrefix.Text + foundRows[i]["COLUMN_NAME"].ToString() + this.textBoxIdentifierPostfix.Text;
                string ParameterName = this.textBoxParameterPrefix.Text + Regex.Replace(foundRows[i]["COLUMN_NAME"].ToString(), "\\W", "_");

                //get column dataType 
                string dataType = foundRows[i]["DATA_TYPE"].ToString();
                if (dataType.ToLower() == "varchar" || dataType.ToLower() == "nvarchar")
                {
                    string characterLength = foundRows[i]["CHARACTER_MAXIMUM_LENGTH"].ToString();
                    if (characterLength == "-1")
                    {
                        characterLength = "max";
                    }
                    dataType += string.Format("({0})", characterLength);
                }
                else if (dataType.ToLower() == "decimal" || dataType.ToLower() == "numeric")
                {
                    string numericPercision = foundRows[i]["NUMERIC_PRECISION"].ToString();
                    string numericScale = foundRows[i]["NUMERIC_SCALE"].ToString();
                    dataType += string.Format("({0},{1}) ", numericPercision, numericScale);
                }



                string parameterDirection = "";
                string columnDeafultvalue = foundRows[i]["COLUMN_DEFAULT"].ToString();
                bool useGetDateInInsert = Regex.IsMatch(columnDeafultvalue, "getdate", RegexOptions.IgnoreCase);
                if (columnDeafultvalue != "")
                {
                    if (columnDeafultvalue.StartsWith("(("))
                    {
                        while (columnDeafultvalue.StartsWith("(") && columnDeafultvalue.EndsWith(")"))
                        {
                            columnDeafultvalue = columnDeafultvalue.Substring(1, columnDeafultvalue.Length - 2);
                        }
                    }
                    else
                    {
                        columnDeafultvalue = "Null";
                    }
                }
                else
                {
                    columnDeafultvalue = "Null";
                }



                string primaryIdentifierfilter = string.Format("TABLE_SCHEMA='{0}' AND TABLE_NAME='{1}' AND COLUMN_NAME='{2}' ", DataBase.EscapeSqlCharacter(Schema, false), DataBase.EscapeSqlCharacter(tableName, false), DataBase.EscapeSqlCharacter(foundRows[i]["COLUMN_NAME"].ToString(), false));
                DataView dv = new DataView(this.constrainsDataTable, primaryIdentifierfilter, "ORDINAL_POSITION", DataViewRowState.CurrentRows);

                string settingfilter = string.Format("TABLE_SCHEMA='{0}' AND TABLE_NAME='{1}' AND COLUMN_NAME='{2}'", DataBase.EscapeSqlCharacter(Schema, false), DataBase.EscapeSqlCharacter(tableName, false), DataBase.EscapeSqlCharacter(foundRows[i]["COLUMN_NAME"].ToString(), false));
                DataRow[] settingfoundRows = this.SettingsDataTable.Select(settingfilter);

                bool isPrimaryKey = (dv.Count > 0);
                bool isIdentity = (foundRows[i]["IS_IDENTITY"].ToString() == "1");
                bool isComputed = (foundRows[i]["IS_COMPUTED"].ToString() == "1");
                bool UseGetDateInInsert = Regex.IsMatch(foundRows[i]["COLUMN_DEFAULT"].ToString(), "getdate", RegexOptions.IgnoreCase);
                bool UseGetDateInUpdate = (settingfoundRows.Length > 0 && Convert.ToBoolean(settingfoundRows[0]["UseGetDate"]));
                bool isTimeStamp = dataType.ToLower().Contains("timestamp");




                if (isIdentity || isComputed)
                {
                    //identityExpression = string.Format("SET {0}=SCOPE_IDENTITY()", ParameterName);
                    parameterDirection = "OUTPUT";
                    if (selectOutputParameters != "")
                    {
                        selectOutputParameters += ", ";
                    }
                    selectOutputParameters += ParameterName + " = " + columnName;
                }
                else if (UseGetDateInInsert)
                {
                    //DateParameters += string.Format("\r\n	SET {0}=getdate()", ParameterName);
                    parameterDirection = "OUTPUT";
                    if (selectOutputParameters != "")
                    {
                        selectOutputParameters += ", ";
                    }
                    selectOutputParameters += ParameterName + " = " + columnName;
                }
                else if (UseGetDateInUpdate)
                {
                    parameterDirection = "OUTPUT";
                    if (selectOutputParameters != "")
                    {
                        selectOutputParameters += ", ";
                    }
                    selectOutputParameters += ParameterName + " = " + columnName;
                    if (UpdatedColumns != "")
                    {
                        UpdatedColumns += ", ";
                    }
                    UpdatedColumns += "\r\n	" + columnName + " =" + this.textBoxSystemDateFunction.Text;
                }
                else if (isTimeStamp)
                {
                    parameterDirection = "OUTPUT";
                    if (selectOutputParameters != "")
                    {
                        selectOutputParameters += ", ";
                    }
                    selectOutputParameters += ParameterName + " = " + columnName;
                    timeStampParameter = ParameterName;
                    timeStampColumn = columnName;
                }
                else
                {
                    //insert columns
                    if (ColumnsConcatenated != "")
                    {
                        ColumnsConcatenated += ", ";
                        ParametersConcatenated += ", ";
                    }
                    ColumnsConcatenated += columnName;
                    ParametersConcatenated += ParameterName;


                    //update columns
                    if (UpdatedColumns != "")
                    {
                        UpdatedColumns += ", ";
                    }
                    UpdatedColumns += "\r\n			" + columnName + " =" + ParameterName;
                }
                if (isPrimaryKey)
                {
                    if (PrimaryIdentifiersWhereCondition != "")
                    {
                        PrimaryIdentifiersWhereCondition += "\r\n	AND ";
                    }
                    else
                    {
                        PrimaryIdentifiersWhereCondition = "	WHERE ";
                    }
                    PrimaryIdentifiersWhereCondition += string.Format("({0} = {1})", columnName, ParameterName);
                }

                //parameters definition
                if (parametersDefinition != "")
                {
                    parametersDefinition += " , ";
                }
                parametersDefinition += string.Format("\r\n	{0} {1} ={2} {3}", ParameterName, dataType, columnDeafultvalue, parameterDirection);
            }
            
            //inserted Parameter
            if (parametersDefinition != "")
            {
                parametersDefinition += " , ";
            }
            parametersDefinition += "\r\n	" + this.textBoxParameterPrefix.Text + "Inserted bit OUTPUT";


            if (timeStampParameter != "")
            {
                if (parametersDefinition != "")
                {
                    parametersDefinition += " , ";
                }
                parametersDefinition += "\r\n	" + this.textBoxParameterPrefix.Text + "OldTimeStamp timeStamp OUTPUT";
                timeStampCheck += "    SELECT " + this.textBoxParameterPrefix.Text + "OldTimeStamp = " + timeStampColumn;
                timeStampCheck += "\r\n		FROM " + tableFullName;
                timeStampCheck += "\r\n    " + PrimaryIdentifiersWhereCondition;
                timeStampCheck += "\r\n\r\n		IF  " + this.textBoxParameterPrefix.Text + "OldTimeStamp = " + timeStampParameter; ;
                timeStampCheck += "\r\n		BEGIN";
                endTimeStampCheck += "\r\n		END";
            }
            if (selectOutputParameters != "")
            {
                selectStatement += "SELECT " + selectOutputParameters
                    + "\r\n" + "    FROM " + tableFullName
                    + "\r\n" + PrimaryIdentifiersWhereCondition;
            }



            string AlterTemplate = @"/*IF EXISTS (SELECT * FROM dbo.Sysobjects where id = object_id(N'_ProcedureName_') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   drop procedure _ProcedureName_*/";
            AlterTemplate = Regex.Replace(AlterTemplate, "_ProcedureName_", ProcedureName, RegexOptions.IgnoreCase);
            if (this.checkBoxUseAlter.Checked)
            {
                AlterTemplate = AlterTemplate.Replace("/*", "").Replace("*/", "");
            }

            string Template = File.ReadAllText(Application.StartupPath + "\\Templates\\Sql Procedures\\InsertUpdateProcedure.sql");
            Template = Regex.Replace(Template, "_Date_", CurrentDate, RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_Author_", Author, RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_DataBaseName_", dataBaseName, RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_TableFullName_", tableFullName, RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_ProcedureName_", ProcedureName, RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_AlterTemplate_", AlterTemplate.Trim(), RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_ParametersDefinition_", parametersDefinition.Trim(), RegexOptions.IgnoreCase);
            //Template = Regex.Replace(Template, "_DateParametersInsert_", DateParametersInsert.Trim(), RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_ColumnsConcatenated_", ColumnsConcatenated.Trim(), RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_ParametersConcatenated_", ParametersConcatenated.Trim(), RegexOptions.IgnoreCase);
            //Template = Regex.Replace(Template, "_identityExpressionInsert_", identityExpressionInsert.Trim(), RegexOptions.IgnoreCase);
            //Template = Regex.Replace(Template, "_identityExpressionUpdate_", identityExpressionUpdate.Trim(), RegexOptions.IgnoreCase);
            //Template = Regex.Replace(Template, "_DateParametersUpdate_", DateParametersUpdate.Trim(), RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_UpdatedColumns_", UpdatedColumns.Trim(), RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_PrimaryIdentifiersWhereCondition_", PrimaryIdentifiersWhereCondition.Trim(), RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_SelectStatement_", selectStatement.Trim(), RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_TimeStampCheck_", timeStampCheck.Trim(), RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_EndtimeStampCheck_", endTimeStampCheck.Trim(), RegexOptions.IgnoreCase);
            Template = Regex.Replace(Template, "_prefix_", this.textBoxParameterPrefix.Text, RegexOptions.IgnoreCase);

            //File.WriteAllText(this.folderBrowserDialog.SelectedPath + "\\SQL\\Insert\\" + ProcedureName.Replace("[", "").Replace("]", "") + ".sql", sb.ToString());
            File.AppendAllText(this.dataLayerPath + "\\SQL\\Tables\\" + Schema + "\\" + tableName + ".sql", Template);
            File.AppendAllText(this.dataLayerPath + "\\SQL\\ALL.sql", "\r\n\r\n\r\n--" + Template);
        }
        //-------------------------------------------------------------------------------------------------       
        #endregion

        #region dataAccesslayer
        //--------------------------------------------------------------------------------------
        private void generateDataLayer(string Schema, string tableName)
        {
            string filter = string.Format("TABLE_SCHEMA='{0}' AND TABLE_NAME='{1}'", DataBase.EscapeSqlCharacter(Schema, false), DataBase.EscapeSqlCharacter(tableName, false));
            foundRows = new DataView(this.columnsDataTable, filter, "ORDINAL_POSITION", DataViewRowState.CurrentRows);

            string CurrentDate = DateTime.Now.ToString("[[yyyyMMddHHmmss]]");
            string Author = "Ahmed Fathi Hamouda Abd El Hafez";
            string dataBaseName = ControlHelper.GetControlProperty(this.comboBoxDatabase, "Text").ToString().Replace(" ", "_");
            dataBaseName = dataBaseName[0].ToString().ToUpper() + dataBaseName.Substring(1);
            string CsClass = this.getCsClass(tableName);
            string DLClass = this.textBoxDataLayerSuffix.Text.Replace("#TABLENAME#", CsClass);
            string BLClass = this.textBoxBusinessLayerSuffix.Text.Replace("#TABLENAME#", CsClass);
            string Procedure = Regex.Replace(this.textBoxsqlSuffix.Text, "#TableName#", tableName, RegexOptions.IgnoreCase);
            string InsertProcedure = Regex.Replace(Procedure, "#Event#", "Insert", RegexOptions.IgnoreCase);
            string UpdateProcedure = Regex.Replace(Procedure, "#Event#", "Update", RegexOptions.IgnoreCase);
            string DeleteProcedure = Regex.Replace(Procedure, "#Event#", "Delete", RegexOptions.IgnoreCase);
            string SelectProcedure = Regex.Replace(Procedure, "#Event#", "Select", RegexOptions.IgnoreCase);
            string InsertUpdateProcedure = Regex.Replace(Procedure, "#Event#", "Insert-Update", RegexOptions.IgnoreCase);

            StringDictionary replacedvalues = new StringDictionary() ;

            if (this.checkBoxInsert.Checked)
            {
                replacedvalues.Add("_InsertProcedure_", InsertProcedure);
            }
            if (this.checkBoxUpdate.Checked)
            {
                replacedvalues.Add("_UpdateProcedure_", UpdateProcedure);
            }
            if (this.checkBoxDelete.Checked)
            {
                replacedvalues.Add("_DeleteProcedure_", DeleteProcedure);
            }
            if (this.checkBoxSelect.Checked)
            {
                replacedvalues.Add("_SelectProcedure_", SelectProcedure);
            }
            if (this.checkBoxInsertUpdate.Checked)
            {
                replacedvalues.Add("_InsertUpdateProcedure_", InsertUpdateProcedure);
            }
            replacedvalues.Add("_DataBaseName_", dataBaseName);
            
            replacedvalues.Add("_Date_", CurrentDate);
            replacedvalues.Add("_Author_", Author);
            replacedvalues.Add("_DataLayerNameSpace_", this.textBoxDataLayer.Text);
            replacedvalues.Add("_TableName_", tableName);
            replacedvalues.Add("_Schema_", Schema);
            replacedvalues.Add("_DLClass_", DLClass);
            replacedvalues.Add("_DLClassStatement_", DLClass);
            replacedvalues.Add("_BLClass_", BLClass);

            string fileTemplate = Application.StartupPath + "\\Templates\\DLClass.cs";
            bool includeSqlStatement = (bool)ControlHelper.GetControlProperty(this.checkBoxIncludeSqlStatement, "Checked");
            if (includeSqlStatement)
            {
                fileTemplate = Application.StartupPath + "\\Templates\\DLClassStatement.cs";
            }
            
            //generate dlcass
            this.generateCsFile(fileTemplate, this.dataLayerPath + "\\Data Layer\\Data Access Classes\\" + DLClass + ".cs"
            , true, false, "Data Layer\\Data Access Classes\\"+DLClass+".cs", replacedvalues);
            //generate dlclassextended
            this.generateCsFile(Application.StartupPath + "\\Templates\\DLClass.Extended.cs", this.dataLayerPath + "\\Extended Classes\\" + DLClass + ".Extended.cs"
            , false, false, "Extended Classes\\" + DLClass + ".Extended.cs", replacedvalues);
        }
        #endregion

        #region Businesslayer
        //--------------------------------------------------------------------------------------
        private void generateBusinessLayer(string Schema, string tableName)
        {
            string filter = string.Format("TABLE_SCHEMA='{0}' AND TABLE_NAME='{1}'", DataBase.EscapeSqlCharacter(Schema, false), DataBase.EscapeSqlCharacter(tableName, false));
            foundRows = new DataView(this.columnsDataTable, filter, "ORDINAL_POSITION", DataViewRowState.CurrentRows);

            string CurrentDate = DateTime.Now.ToString("[[yyyyMMddHHmmss]]");
            string Author = "Ahmed Fathi Hamouda Abd El Hafez";
            string dataBaseName = ControlHelper.GetControlProperty(this.comboBoxDatabase, "Text").ToString().Replace(" ", "_");
            dataBaseName = dataBaseName[0].ToString().ToUpper() + dataBaseName.Substring(1);
            string CsClass = this.getCsClass(tableName);
            string DLClass = this.textBoxDataLayerSuffix.Text.Replace("#TABLENAME#", CsClass);
            string BLClass = this.textBoxBusinessLayerSuffix.Text.Replace("#TABLENAME#", CsClass);

            StringDictionary replacedvalues = new StringDictionary(); ;
            replacedvalues.Add("_DataBaseName_", dataBaseName);
            replacedvalues.Add("_Date_", CurrentDate);
            replacedvalues.Add("_Author_", Author);
            replacedvalues.Add("_DataLayerNameSpace_", this.textBoxDataLayer.Text);
            replacedvalues.Add("_BusinessNameSpace_", this.textBoxBusinessLayer.Text);
            replacedvalues.Add("_TableName_", tableName);
            replacedvalues.Add("_Schema_", Schema);
            replacedvalues.Add("_DLClass_", DLClass);
            replacedvalues.Add("_DLClassStatement_", DLClass);
            replacedvalues.Add("_BLClass_", BLClass);

            this.generateCsFile(Application.StartupPath + "\\Templates\\BLClass.cs", this.BusinessLayerPath + "\\Business Layer\\Business Classes\\" + BLClass + ".cs"
            , true, true, "Business Layer\\Business Classes\\" + BLClass + ".cs", replacedvalues);

            this.generateCsFile(Application.StartupPath + "\\Templates\\BLClass.Extended.cs", this.BusinessLayerPath + "\\Extended Classes\\Business Classes\\" + BLClass + ".Extended.cs"
            , false, true, "Extended Classes\\Business Classes\\" + BLClass + ".Extended.cs", replacedvalues);

            this.generateCsFile(Application.StartupPath + "\\Templates\\BLClassCollection.cs", this.BusinessLayerPath + "\\Business Layer\\Business Collection\\" + BLClass + "Collection.cs"
            , true, true, "Business Layer\\Business Collection\\" + BLClass + "Collection.cs", replacedvalues);

            this.generateCsFile(Application.StartupPath + "\\Templates\\BLClassCollection.Extended.cs", this.BusinessLayerPath + "\\Extended Classes\\Business Collection\\" + BLClass + "Collection.Extended.cs"
            , false, true, "Extended Classes\\Business Collection\\" + BLClass + "Collection.Extended.cs", replacedvalues);

            if (this.checkBoxRelatedObjects.Checked)
            {
                this.generateCsFile(Application.StartupPath + "\\Templates\\BLClass.RelatedObjects.cs", this.BusinessLayerPath + "\\Business Layer\\Business Related Objects\\" + BLClass + ".RelatedObjects.cs"
                , true, true, "Business Layer\\Business Related Objects\\" + BLClass + ".RelatedObjects.cs", replacedvalues);
            }

            if (this.checkBoxRelatedCollections.Checked)
            {
                this.generateCsFile(Application.StartupPath + "\\Templates\\BLClass.RelatedCollections.cs", this.BusinessLayerPath + "\\Business Layer\\Business Related Collections\\" + BLClass + ".RelatedCollections.cs"
                , true, true, "Business Layer\\Business Related Collections\\" + BLClass + ".RelatedCollections.cs", replacedvalues);
            }
        }
        #endregion

        #region other methods
        //--------------------------------------------------------------------------------------
        private void getDataBaseInformation()
        {
            string databaseName = this.comboBoxDatabase.Text.Replace(" ", "_");
            if (this.steadystate && databaseName.Trim() != "")
            {
                this.textBoxDataLayer.Text = databaseName[0].ToString().ToUpper() + databaseName.Substring(1) + "DataLayer";
                this.textBoxBusinessLayer.Text = databaseName[0].ToString().ToUpper() + databaseName.Substring(1) + "BusinessLayer";

                this.ExtendedDataGridViewTables.MyDataGridView.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                this.ExtendedDataGridViewTables.MyDataGridView.AlternatingRowsDefaultCellStyle.BackColor = Color.AliceBlue;
                this.ExtendedDataGridViewTables.MyDataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
                if (this.textBoxUserID.Text.Trim() != "")
                {
                    SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder { DataSource = this.textBoxServer.Text, InitialCatalog = this.comboBoxDatabase.Text, UserID = this.textBoxUserID.Text, Password = this.textBoxPassword.Text };
                    this.currentconnection = new DataBase(scsb.ConnectionString); ;
                }
                else
                {
                    SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder { DataSource = this.textBoxServer.Text, InitialCatalog = this.comboBoxDatabase.Text, IntegratedSecurity = true };
                    this.currentconnection = new DataBase(scsb.ConnectionString); ;
                }
                string Query = @"SELECT CAST(1 AS bit) AS Choose,* FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_TYPE,TABLE_NAME, TABLE_SCHEMA";
                this.tablesDataTable = this.currentconnection.GetDataTable(Query, CommandType.Text, ref error);
                this.ExtendedDataGridViewTables.DataSource = this.tablesDataTable;
                if (this.error != null)
                {
                    MessageBox.Show(this.error.Message);
                }
                else
                {
                    Query = @"SELECT INFORMATION_SCHEMA.COLUMNS.*,
	                COL_LENGTH(TABLE_SCHEMA+'.'+TABLE_NAME, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME) AS COLUMN_LENGTH,
	                COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsComputed') AS IS_COMPUTED,
	                COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsIdentity') AS IS_IDENTITY,
	                COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsRowGuidCol') AS IS_ROWGUIDCOL
                    FROM INFORMATION_SCHEMA.COLUMNS";
                    this.columnsDataTable = this.currentconnection.GetDataTable(Query, CommandType.Text, ref error);

                    Query = @"SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT,CAST(0 as bit) as useGetDate 
                        FROM INFORMATION_SCHEMA.COLUMNS
                        WHERE ((DATA_TYPE = 'datetime'))
                        ORDER BY DATA_TYPE,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME";
                    this.SettingsDataTable = this.currentconnection.GetDataTable(Query, CommandType.Text, ref error);
                    this.extendedDataGridViewSettings.DataSource = this.SettingsDataTable;
                    if (this.error != null)
                    {
                        MessageBox.Show(this.error.Message);
                    }
                    else
                    {
                        Query = @"SELECT DISTINCT 
                      PrimaryTable.CONSTRAINT_SCHEMA, PrimaryTable.CONSTRAINT_NAME, PrimaryTable.TABLE_SCHEMA, PrimaryTable.TABLE_NAME, PrimaryTable.COLUMN_NAME, 
                      PrimaryTable.ORDINAL_POSITION, SecondaryTable.TABLE_SCHEMA AS ReferenceSchema, SecondaryTable.TABLE_NAME AS ReferenceTable, 
                      SecondaryTable.COLUMN_NAME AS ReferenceColumn, SecondaryTable.ORDINAL_POSITION AS RefernceOrdinal
FROM         INFORMATION_SCHEMA.Key_COLUMN_USAGE AS PrimaryTable INNER JOIN
                      INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON 
                      PrimaryTable.CONSTRAINT_CATALOG = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_CATALOG AND 
                      PrimaryTable.CONSTRAINT_SCHEMA = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_SCHEMA AND 
                      PrimaryTable.CONSTRAINT_NAME = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME LEFT OUTER JOIN
                      INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ON 
                      INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_CATALOG = PrimaryTable.CONSTRAINT_CATALOG AND 
                      INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_SCHEMA = PrimaryTable.CONSTRAINT_SCHEMA AND 
                      INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_NAME = PrimaryTable.CONSTRAINT_NAME LEFT OUTER JOIN
                      INFORMATION_SCHEMA.key_COLUMN_USAGE AS SecondaryTable ON 
                      INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.CONSTRAINT_CATALOG = SecondaryTable.CONSTRAINT_CATALOG AND 
                      INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.CONSTRAINT_SCHEMA = SecondaryTable.CONSTRAINT_SCHEMA AND 
                      INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME = SecondaryTable.CONSTRAINT_NAME
WHERE     (INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'Primary key')                      ";
                        this.constrainsDataTable = this.currentconnection.GetDataTable(Query, CommandType.Text, ref error);

                        if (this.error != null)
                        {
                            MessageBox.Show(this.error.Message);
                        }
                    }
                }
            }
        }
        private void extract()
        {
            this.DataLayerProjectFiles = "";
            this.BusinessLayerProjectFiles = "";

            if (this.checkBoxgenerateDataLayerProject.Checked)
            {
                this.dataLayerPath = this.folderBrowserDialog.SelectedPath + "\\" + this.textBoxDataLayer.Text;

                PathHelper ep = new PathHelper();
                ep.FullPath = Application.StartupPath + "\\_DummyProject_DataLayer";
                Exception error = ep.CopyAs(this.dataLayerPath, false);

                this.changeFile(this.dataLayerPath + "\\_DummyProject_DataLayer.csproj", true, true, "_DummyProject_DataLayer", this.textBoxDataLayer.Text);
                this.changeFile(this.dataLayerPath + "\\debug\\_DummyProject_DataLayer.dll", true, false, "_DummyProject_DataLayer", this.textBoxDataLayer.Text);


                this.changeFile(this.dataLayerPath + "\\Properties\\Resources.Designer.cs", true, true, "_DummyProject_DataLayer", this.textBoxDataLayer.Text);
                this.changeFile(this.dataLayerPath + "\\Properties\\Resources.resx", true, true, "_DummyProject_DataLayer", this.textBoxDataLayer.Text);
                this.changeFile(this.dataLayerPath + "\\Properties\\Settings.Designer.cs", true, true, "_DummyProject_DataLayer", this.textBoxDataLayer.Text);
                this.changeFile(this.dataLayerPath + "\\Properties\\Settings.settings", true, true, "_DummyProject_DataLayer", this.textBoxDataLayer.Text);
            }
            else
            {
                this.dataLayerPath = this.folderBrowserDialog.SelectedPath;
            }
            if (this.checkBoxGenerateBusinessProject.Checked)
            {
                this.BusinessLayerPath = this.folderBrowserDialog.SelectedPath + "\\" + this.textBoxBusinessLayer.Text;

                PathHelper ep = new PathHelper();
                ep.FullPath = Application.StartupPath + "\\_DummyProject_BusinessLayer";
                Exception error = ep.CopyAs(this.BusinessLayerPath, false);



                this.changeFile(this.BusinessLayerPath + "\\_DummyProject_BusinessLayer.csproj", false, true, "_DummyProject_DataLayer", this.textBoxDataLayer.Text);
                this.changeFile(this.BusinessLayerPath + "\\_DummyProject_BusinessLayer.csproj", true, true, "_DummyProject_BusinessLayer", this.textBoxBusinessLayer.Text);
                this.changeFile(this.BusinessLayerPath + "\\debug\\_DummyProject_BusinessLayer.dll", true, false, "_DummyProject_BusinessLayer", this.textBoxBusinessLayer.Text);
                this.changeFile(this.BusinessLayerPath + "\\debug\\_DummyProject_DataLayer.dll", true, false, "_DummyProject_DataLayer", this.textBoxDataLayer.Text);


                this.changeFile(this.BusinessLayerPath + "\\Properties\\Resources.Designer.cs", true, true, "_DummyProject_BusinessLayer", this.textBoxBusinessLayer.Text);
                this.changeFile(this.BusinessLayerPath + "\\Properties\\Resources.resx", true, true, "_DummyProject_BusinessLayer", this.textBoxBusinessLayer.Text);
                this.changeFile(this.BusinessLayerPath + "\\Properties\\Settings.Designer.cs", true, true, "_DummyProject_BusinessLayer", this.textBoxBusinessLayer.Text);
                this.changeFile(this.BusinessLayerPath + "\\Properties\\Settings.settings", true, true, "_DummyProject_BusinessLayer", this.textBoxBusinessLayer.Text);
            }
            else
            {
                this.BusinessLayerPath = this.folderBrowserDialog.SelectedPath;
            }

            if (this.checkBoxInsert.Checked || this.checkBoxUpdate.Checked || this.checkBoxDelete.Checked || this.checkBoxSelect.Checked)
            {
                Directory.CreateDirectory(this.dataLayerPath + "\\SQL\\Tables");
                File.WriteAllText(this.dataLayerPath + "\\SQL\\All.sql", "USE [" + ControlHelper.GetControlProperty(this.comboBoxDatabase, "Text") + "]", Encoding.UTF8);
                this.DataLayerProjectFiles += string.Format("    <Content Include=\"SQL\\ALL.sql\" />\r\n");
            }

            string CurrentDate = DateTime.Now.ToString("[[yyyyMMddHHmmss]]");
            string Author = "Ahmed Fathi Hamouda Abd El Hafez";
            string dataBaseName = ControlHelper.GetControlProperty(this.comboBoxDatabase, "Text").ToString().Replace(" ", "_");
            dataBaseName = dataBaseName[0].ToString().ToUpper() + dataBaseName.Substring(1);
            if (this.checkBoxDataLayer.Checked)
            {
                Directory.CreateDirectory(this.dataLayerPath + "\\Data Layer\\General Classes");
                Directory.CreateDirectory(this.dataLayerPath + "\\Data Layer\\Data Access Classes");


                StringDictionary replacedvalues = new StringDictionary();
                replacedvalues.Add("_Date_", CurrentDate);
                replacedvalues.Add("_Author_", Author);
                replacedvalues.Add("_DataLayerNameSpace_", this.textBoxDataLayer.Text);
                replacedvalues.Add("_DataBaseName_", dataBaseName);

                this.generateCsFile(Application.StartupPath + "\\Templates\\DataBaseConnections.cs", this.dataLayerPath + "\\Data Layer\\General Classes\\DataBaseConnections.cs"
                    , true, false, "Data Layer\\General Classes\\DataBaseConnections.cs", replacedvalues);
                this.generateCsFile(Application.StartupPath + "\\Templates\\Settings.cs", this.dataLayerPath + "\\Data Layer\\General Classes\\Settings.cs"
                    , true, false, "Data Layer\\General Classes\\Settings.cs", replacedvalues);
                this.generateCsFile(Application.StartupPath + "\\Templates\\DataFilter.cs", this.dataLayerPath + "\\Data Layer\\General Classes\\DataFilter.cs"
                    , true, false, "Data Layer\\General Classes\\DataFilter.cs", replacedvalues);
                this.generateCsFile(Application.StartupPath + "\\Templates\\DataSort.cs", this.dataLayerPath + "\\Data Layer\\General Classes\\DataSort.cs"
                    , true, false, "Data Layer\\General Classes\\DataSort.cs", replacedvalues);
                this.generateCsFile(Application.StartupPath + "\\Templates\\LogicalOperators.cs", this.dataLayerPath + "\\Data Layer\\General Classes\\LogicalOperators.cs"
                    , true, false, "Data Layer\\General Classes\\LogicalOperators.cs", replacedvalues);
                this.generateCsFile(Application.StartupPath + "\\Templates\\FilterOperators.cs", this.dataLayerPath + "\\Data Layer\\General Classes\\FilterOperators.cs"
                    , true, false, "Data Layer\\General Classes\\FilterOperators.cs", replacedvalues);
            }
            if (this.checkBoxBusinessLayer.Checked)
            {
                Directory.CreateDirectory(this.BusinessLayerPath + "\\Business Layer\\General Classes");
                Directory.CreateDirectory(this.BusinessLayerPath + "\\Business Layer\\Business Classes");
                Directory.CreateDirectory(this.BusinessLayerPath + "\\Business Layer\\Business Collection");
                Directory.CreateDirectory(this.BusinessLayerPath + "\\Business Layer\\Business Related Collections");
                Directory.CreateDirectory(this.BusinessLayerPath + "\\Business Layer\\Business Related Objects");



                StringDictionary replacedvalues = new StringDictionary();
                replacedvalues.Add("_Date_", CurrentDate);
                replacedvalues.Add("_Author_", Author);
                replacedvalues.Add("_BusinessNameSpace_", this.textBoxBusinessLayer.Text);

                this.generateCsFile(Application.StartupPath + "\\Templates\\SaveType.cs", this.BusinessLayerPath + "\\Business Layer\\General Classes\\SaveType.cs"
                    , true, true, "Business Layer\\General Classes\\SaveType.cs", replacedvalues);
                this.generateCsFile(Application.StartupPath + "\\Templates\\BusinessObjectState.cs", this.BusinessLayerPath + "\\Business Layer\\General Classes\\BusinessObjectState.cs"
                    , true, true, "Business Layer\\General Classes\\BusinessObjectState.cs", replacedvalues);
            }
            DataRow[] FoundRows = this.tablesDataTable.Select("Choose=1");
            this.extendedProgressPar1.Maximum = FoundRows.Length;

            for (int i = 0; i < FoundRows.Length; i++)
            {
                string Schema = FoundRows[i]["TABLE_SCHEMA"].ToString();
                string Name = FoundRows[i]["TABLE_NAME"].ToString();
                this.extendedProgressPar1.Value = i + 1;

                Directory.CreateDirectory(this.dataLayerPath + "\\SQL\\Tables\\" + Schema);
                File.WriteAllText(this.dataLayerPath + "\\SQL\\Tables\\" + Schema + "\\" + Name + ".sql", "");
                this.DataLayerProjectFiles += string.Format("    <Content Include=\"SQL\\Tables\\{0}\\{1}.sql\" />\r\n", Schema, Name);

                if (this.checkBoxInsert.Checked)
                {
                    this.extendedProgressPar1.Description = Schema + "." + Name + "\t\t   Insert";
                    this.generateInsertProcedure(Schema, Name);
                }
                if (this.checkBoxUpdate.Checked)
                {
                    this.extendedProgressPar1.Description = Schema + "." + Name + "\t\t   Update";
                    this.generateUpdateProcedure(Schema, Name);
                }
                if (this.checkBoxInsertUpdate.Checked)
                {
                    this.extendedProgressPar1.Description = Schema + "." + Name + "\t\t   Insert_Update";
                    this.generateInsertUpdateProcedure(Schema, Name);
                }
                if (this.checkBoxDelete.Checked)
                {
                    this.extendedProgressPar1.Description = Schema + "." + Name + "\t\t   Delete";
                    this.generateDeleteProcedure(Schema, Name);
                }
                if (this.checkBoxSelect.Checked)
                {
                    this.extendedProgressPar1.Description = Schema + "." + Name + "\t\t   Select";
                    this.generateSelectProcedure(Schema, Name);
                }
                if (this.checkBoxDataLayer.Checked)
                {
                    this.extendedProgressPar1.Description = Schema + "." + Name + "\t\t   DataLayer";
                    this.generateDataLayer(Schema, Name);
                }
                if (this.checkBoxBusinessLayer.Checked)
                {
                    this.extendedProgressPar1.Description = Schema + "." + Name + "\t\t   BusinessLayer";
                    this.generateBusinessLayer(Schema, Name);
                }
            }

            if (this.checkBoxgenerateDataLayerProject.Checked)
            {
                this.changeFile(this.folderBrowserDialog.SelectedPath + "\\" + this.textBoxDataLayer.Text + "\\" + this.textBoxDataLayer.Text + ".csproj", false, true, "<!--_Project_Files_-->", this.DataLayerProjectFiles);
            }
            if (this.checkBoxGenerateBusinessProject.Checked)
            {
                this.changeFile(this.folderBrowserDialog.SelectedPath + "\\" + this.textBoxBusinessLayer.Text + "\\" + this.textBoxBusinessLayer.Text + ".csproj", false, true, "<!--_Project_Files_-->", this.BusinessLayerProjectFiles);
            }

            if (File.Exists(this.dataLayerPath + "\\_DummyProject_DataLayer.csproj"))
            {
                File.Delete(this.dataLayerPath + "\\_DummyProject_DataLayer.csproj");
            }
            if (File.Exists(this.BusinessLayerPath + "\\_DummyProject_BusinessLayer.csproj"))
            {
                File.Delete(this.BusinessLayerPath + "\\_DummyProject_BusinessLayer.csproj");
            }
        }
        private string convertSqlTypeToCSharpType(string sqlType, string length, bool allowNullable)
        {
            string cSharpType = "";
            if (sqlType.ToLower() == "bit")
            {
                cSharpType = "bool";
                if (allowNullable)
                {
                    cSharpType += "?";
                }
            }
            else if (sqlType.ToLower() == "tinyint")
            {
                cSharpType = "byte";
                if (allowNullable)
                {
                    cSharpType += "?";
                }
            }

            else if (sqlType.ToLower() == "smallint")
            {
                cSharpType = "short";
                if (allowNullable)
                {
                    cSharpType += "?";
                }
            }
            else if (sqlType.ToLower() == "int")
            {
                cSharpType = "int";
                if (allowNullable)
                {
                    cSharpType += "?";
                }
            }
            else if (sqlType.ToLower() == "bigint")
            {
                cSharpType = "long";
                if (allowNullable)
                {
                    cSharpType += "?";
                }
            }
            else if (sqlType.ToLower() == "float")
            {
                cSharpType = "double";
                if (allowNullable)
                {
                    cSharpType += "?";
                }
            }
            else if (sqlType.ToLower() == "real")
            {
                cSharpType = "float";
                if (allowNullable)
                {
                    cSharpType += "?";
                }
            }
            else if (sqlType.ToLower() == "decimal")
            {
                cSharpType = "decimal";
                if (allowNullable)
                {
                    cSharpType += "?";
                }
            }
            else if (sqlType.ToLower() == "numeric")
            {
                cSharpType = "decimal";
                if (allowNullable)
                {
                    cSharpType += "?";
                }
            }
            else if (sqlType.ToLower() == "smallmoney")
            {
                cSharpType = "decimal";
                if (allowNullable)
                {
                    cSharpType += "?";
                }
            }
            else if (sqlType.ToLower() == "money")
            {
                cSharpType = "decimal";
                if (allowNullable)
                {
                    cSharpType += "?";
                }
            }

            else if (sqlType.ToLower() == "time")
            {
                cSharpType = "TimeSpan";
                if (allowNullable)
                {
                    cSharpType += "?";
                }
            }
            else if (sqlType.ToLower() == "date")
            {
                cSharpType = "DateTime";
                if (allowNullable)
                {
                    cSharpType += "?";
                }
            }
            else if (sqlType.ToLower() == "smalldatetime")
            {
                cSharpType = "DateTime";
                if (allowNullable)
                {
                    cSharpType += "?";
                }
            }
            else if (sqlType.ToLower() == "datetime")
            {
                cSharpType = "DateTime";
                if (allowNullable)
                {
                    cSharpType += "?";
                }
            }
            else if (sqlType.ToLower() == "datetime2")
            {
                cSharpType = "DateTime";
                if (allowNullable)
                {
                    cSharpType += "?";
                }
            }
            else if (sqlType.ToLower() == "datetimeoffse")
            {
                cSharpType = "DateTimeOffset";
                if (allowNullable)
                {
                    cSharpType += "?";
                }
            }

            else if (sqlType.ToLower() == "char")
            {
                cSharpType = "string";
            }
            else if (sqlType.ToLower() == "nchar")
            {
                cSharpType = "string";
            }
            else if (sqlType.ToLower() == "varchar")
            {
                cSharpType = "string";
            }
            else if (sqlType.ToLower() == "nvarchar")
            {
                cSharpType = "string";
            }
            else if (sqlType.ToLower() == "text")
            {
                cSharpType = "string";
            }
            else if (sqlType.ToLower() == "ntext")
            {
                cSharpType = "string";
            }
            else if (sqlType.ToLower() == "xml")
            {
                cSharpType = "string";
            }

            else if (sqlType.ToLower() == "sysname")
            {
                cSharpType = "byte[]";
            }
            else if (sqlType.ToLower() == "binary")
            {
                cSharpType = "byte[]";
            }
            else if (sqlType.ToLower() == "varbinary")
            {
                cSharpType = "byte[]";
            }
            else if (sqlType.ToLower() == "image")
            {
                cSharpType = "byte[]";
            }
            else if (sqlType.ToLower() == "timestamp")
            {
                cSharpType = "byte[]";
            }
            else if (sqlType.ToLower() == "rowversion")
            {
                cSharpType = "byte[]";
            }
            else if (sqlType.ToLower() == "FILESTREAM")
            {
                cSharpType = "byte[]";
            }
            else if (sqlType.ToLower() == "sql_variant")
            {
                cSharpType = "object";
            }
            else if (sqlType.ToLower() == "uniqueidentifier")
            {
                cSharpType = "Guid";
                if (allowNullable)
                {
                    cSharpType += "?";
                }
            }
            else
            {
                cSharpType = "object";
            }
            return cSharpType;

        }
        private string convertSqlTypeToDbTypeEnum(string sqlType)
        {
            string dbTypeEnum = DbType.String.ToString();
            if (sqlType.ToLower() == "bit")
            {
                dbTypeEnum = "DbType." + DbType.Boolean.ToString();
            }
            else if (sqlType.ToLower() == "tinyint")
            {
                dbTypeEnum = "DbType." + DbType.Byte.ToString();
            }
            else if (sqlType.ToLower() == "smallint")
            {
                dbTypeEnum = "DbType." + DbType.Int16.ToString();
            }
            else if (sqlType.ToLower() == "int")
            {
                dbTypeEnum = "DbType." + DbType.Int32.ToString();
            }
            else if (sqlType.ToLower() == "bigint")
            {
                dbTypeEnum = "DbType." + DbType.Int64.ToString();
            }
            else if (sqlType.ToLower() == "float")
            {
                dbTypeEnum = "DbType." + DbType.Single.ToString();
            }
            else if (sqlType.ToLower() == "real")
            {
                dbTypeEnum = "DbType." + DbType.Double.ToString();
            }
            else if (sqlType.ToLower() == "decimal")
            {
                dbTypeEnum = "DbType." + DbType.Decimal.ToString();
            }
            else if (sqlType.ToLower() == "numeric")
            {
                dbTypeEnum = "DbType." + DbType.Decimal.ToString();
            }
            else if (sqlType.ToLower() == "smallmoney")
            {
                dbTypeEnum = "DbType." + DbType.Decimal.ToString();
            }
            else if (sqlType.ToLower() == "money")
            {
                dbTypeEnum = "DbType." + DbType.Decimal.ToString();
            }

            else if (sqlType.ToLower() == "time")
            {
                dbTypeEnum = "DbType." + DbType.Time.ToString();
            }
            else if (sqlType.ToLower() == "date")
            {
                dbTypeEnum = "DbType." + DbType.Date.ToString();
            }
            else if (sqlType.ToLower() == "smalldatetime")
            {
                dbTypeEnum = "DbType." + DbType.DateTime.ToString();
            }
            else if (sqlType.ToLower() == "datetime")
            {
                dbTypeEnum = "DbType." + DbType.DateTime.ToString();
            }
            else if (sqlType.ToLower() == "datetime2")
            {
                dbTypeEnum = "DbType." + DbType.DateTime2.ToString();
            }
            else if (sqlType.ToLower() == "datetimeoffset")
            {
                dbTypeEnum = "DbType." + DbType.DateTimeOffset.ToString();
            }
            else if (sqlType.ToLower() == "timestamp")
            {
                dbTypeEnum = "DbType." + DbType.Binary.ToString();
            }
            else if (sqlType.ToLower() == "rowversion")
            {
                dbTypeEnum = "DbType." + DbType.Binary.ToString();
            }

            else if (sqlType.ToLower() == "char")
            {
                dbTypeEnum = "DbType." + DbType.AnsiStringFixedLength.ToString();
            }
            else if (sqlType.ToLower() == "nchar")
            {
                dbTypeEnum = "DbType." + DbType.StringFixedLength.ToString();
            }
            else if (sqlType.ToLower() == "varchar")
            {
                dbTypeEnum = "DbType." + DbType.AnsiString.ToString();
            }
            else if (sqlType.ToLower() == "nvarchar")
            {
                dbTypeEnum = "DbType." + DbType.String.ToString();
            }
            else if (sqlType.ToLower() == "text")
            {
                dbTypeEnum = "DbType." + DbType.String.ToString();
            }
            else if (sqlType.ToLower() == "ntext")
            {
                dbTypeEnum = "DbType." + DbType.String.ToString();
            }
            else if (sqlType.ToLower() == "sysname")
            {
                dbTypeEnum = "DbType." + DbType.Object.ToString();
            }
            else if (sqlType.ToLower() == "xml")
            {
                dbTypeEnum = "DbType." + DbType.Xml.ToString();
            }

            else if (sqlType.ToLower() == "binary")
            {
                dbTypeEnum = "DbType." + DbType.Binary.ToString();
            }
            else if (sqlType.ToLower() == "varbinary")
            {
                dbTypeEnum = "DbType." + DbType.Binary.ToString();
            }
            else if (sqlType.ToLower() == "image")
            {
                dbTypeEnum = "DbType." + DbType.Binary.ToString();
            }
            else if (sqlType.ToLower() == "FILESTREAM")
            {
                dbTypeEnum = "DbType." + DbType.Binary.ToString();
            }
            else if (sqlType.ToLower() == "sql_variant")
            {
                dbTypeEnum = "DbType." + DbType.Object.ToString();
            }
            else if (sqlType.ToLower() == "uniqueidentifier")
            {
                dbTypeEnum = "DbType." + DbType.Guid.ToString();
            }
            return dbTypeEnum;
        }
        private void changeFile(string fullPath, bool changeName, bool changeContent, string OldValue, string NewValue)
        {
            if (changeName)
            {
                FileInfo fi = new FileInfo(fullPath);
                PathHelper ep = new PathHelper();
                ep.FullPath = fullPath;
                string newName = Regex.Replace(fi.Name, OldValue, NewValue);
                error = ep.Rename(newName);
                fullPath = fi.Directory.FullName + "\\" + newName;
            }
            if (changeContent)
            {
                string content = File.ReadAllText(fullPath);
                content = Regex.Replace(content, OldValue, NewValue, RegexOptions.IgnoreCase);
                File.WriteAllText(fullPath, content, Encoding.UTF8);
            }
        }
        private string getCsClass(string tableName)
        {
            string CsClass = Regex.Replace(tableName, "\\W", "_", RegexOptions.IgnoreCase);
            CsClass = CsClass[0].ToString().ToUpper() + CsClass.Substring(1);
            CsClass = Regex.Replace(CsClass, "xes_|xes\\b", "x", RegexOptions.IgnoreCase);
            CsClass = Regex.Replace(CsClass, "ies_|ies\\b", "y", RegexOptions.IgnoreCase);
            CsClass = Regex.Replace(CsClass, "s_\\B", "_", RegexOptions.IgnoreCase);
            CsClass = Regex.Replace(CsClass, "s\\b", "", RegexOptions.IgnoreCase);
            return CsClass;
        }
        private string getCsProperty(string columnName)
        {
            string CsProperty = Regex.Replace(columnName, "\\W", "_");

            CsProperty = CsProperty[0].ToString().ToUpper() + CsProperty.Substring(1);
            CsProperty = Regex.Replace(CsProperty, "TableName", "Tablename", RegexOptions.IgnoreCase);
            CsProperty = Regex.Replace(CsProperty, "TableSchema", "Tableschema", RegexOptions.IgnoreCase);
            CsProperty = Regex.Replace(CsProperty, "TableFullName", "Tablefullname", RegexOptions.IgnoreCase);
            return CsProperty;
        }
        private void generateCsFile(string fileTemplate, string newFilePath, bool OverWrite, bool forBusiness, string IncludePath, StringDictionary ReplacedValue)
        {
            string template = File.ReadAllText(fileTemplate);
            IEnumerator keysEnumerator = ReplacedValue.Keys.GetEnumerator();
            keysEnumerator.Reset();
            for (int i = 0; i < ReplacedValue.Count; i++)
            {
                keysEnumerator.MoveNext();
                template = Regex.Replace(template, keysEnumerator.Current.ToString(), ReplacedValue[keysEnumerator.Current.ToString()], RegexOptions.IgnoreCase);
            }
            //optional columns
            template = Regex.Replace(template, "#region\\s+Optional[^#]*#endregion\\s+Optional", this.evaluator, RegexOptions.IgnoreCase);

            if (!File.Exists(newFilePath))
            {
                File.WriteAllText(newFilePath, template);
            }
            else if (OverWrite)
            {
                string s = File.ReadAllText(newFilePath);
                if (Regex.Replace(s, "\\[\\[\\d+\\]\\]", "") != Regex.Replace(template, "\\[\\[\\d+\\]\\]", ""))
                {
                    File.WriteAllText(newFilePath, template);
                }
            }
            if (forBusiness)
            {
                this.BusinessLayerProjectFiles += string.Format("    <Compile Include=\"{0}\" />\r\n", IncludePath);
            }
            else
            {
                this.DataLayerProjectFiles += string.Format("    <Compile Include=\"{0}\" />\r\n", IncludePath);
            }
        }
        private string evaluator(Match m)
        {
            if (m.Value.ToLower().Contains("_HasTimeStamp_,_PrimaryKeyProperty_".ToLower()) && foundRows[0]["TABLE_NAME"].ToString().ToLower().Contains("sysdiagram".ToLower()))
            {

            }
            string s = "";
            //bool secondTime = false;
            string Operator = "=";

            string hasOutputParameterInsert = "";
            string hasOutputParameterUpdate = "";
            string hasOutputParameterInsertUpdate = "";
            string hasTimeStamp = "";
            string hasPrimaryKey = "";
            string getDateInInsert = "";
            string getDateInUpdate = "";


            if (foundRows.ToTable().Select("(IS_IDENTITY=1) OR (IS_COMPUTED=1) OR (COLUMN_DEFAULT like '%getdate%') OR (DATA_TYPE like '%timestamp%') ").Length > 0)
            {
                hasOutputParameterInsert = "true;";
                hasOutputParameterInsertUpdate = "true";
            }
            string filter = string.Format("TABLE_SCHEMA='{0}' AND TABLE_NAME='{1}'", DataBase.EscapeSqlCharacter(foundRows[0]["TABLE_SCHEMA"].ToString(), false), DataBase.EscapeSqlCharacter(foundRows[0]["TABLE_NAME"].ToString(), false));
            if (foundRows.ToTable().Select("(IS_IDENTITY=1) OR (IS_COMPUTED=1) OR (DATA_TYPE like '%timestamp%')").Length > 0 || this.SettingsDataTable.Select(filter).Length > 0)
            {
                hasOutputParameterUpdate = "true;";
                hasOutputParameterInsertUpdate = "true";
            }
            if (foundRows.ToTable().Select("(DATA_TYPE like '%timestamp%')").Length > 0)
            {
                hasTimeStamp = "true";
            }
            if (this.constrainsDataTable.Select(string.Format("TABLE_SCHEMA='{0}' AND TABLE_NAME='{1}' ", DataBase.EscapeSqlCharacter(foundRows[0]["TABLE_SCHEMA"].ToString(), false), DataBase.EscapeSqlCharacter(foundRows[0]["TABLE_NAME"].ToString(), false))).Length > 0)
            {
                hasPrimaryKey="true";
            }

            for (int i = 0; i < foundRows.Count; i++)
            {
                string onlyOne = "";
                if (i == 0)
                {
                    onlyOne = "true";
                }
                string template = Regex.Replace(m.Value, "#region\\s+Optional\\s+(@[^@]*@)?", "", RegexOptions.IgnoreCase);
                template = Regex.Replace(template, "#endregion\\s+Optional\\s*", "", RegexOptions.IgnoreCase);
                Match optionalMatch = Regex.Match(m.Value, "@[^@]*@");
                List<string> OptionalKeywords = new List<string>();
                if (optionalMatch.Success)
                {
                    OptionalKeywords.AddRange(optionalMatch.Value.Replace("@", "").ToLower().Split(','));
                    template = template.Replace(optionalMatch.Value, "");
                }

                //get property name
                string columnName = foundRows[i]["COLUMN_NAME"].ToString();
                string columnOrder = foundRows[i]["ORDINAL_POSITION"].ToString();
                string CsProperty = this.getCsProperty(columnName);
                string CsField = "f_" + CsProperty;

                //get csharp type
                string dataType = foundRows[i]["DATA_TYPE"].ToString();
                string DbType = this.convertSqlTypeToDbTypeEnum(dataType);
                string characterLength = foundRows[i]["CHARACTER_MAXIMUM_LENGTH"].ToString();
                bool allowNullable = true; //(foundRows[i]["IS_NULLABLE"].ToString().ToLower() == "yes");
                string CsharpType = this.convertSqlTypeToCSharpType(dataType, characterLength, allowNullable);
                string PName = CsProperty;
                string parameterNumber = "Prm_" + CsProperty;

                string PDirection = "ParameterDirection.Input";
                string PDirectionInsert = "ParameterDirection.Input";
                string PDirectionUpdate = "ParameterDirection.Input";
                string PDirectionInsertUpdate = "ParameterDirection.Input";

                string outputParameterInsertProperty = "";
                string outputParameterUpdateProperty = "";
                string outputParameterInsertUpdateProperty = "";
                string timeStampProperty = "";
                string PrimaryKeyProperty = "";

                string primaryIdentifierfilter = string.Format("TABLE_SCHEMA='{0}' AND TABLE_NAME='{1}' AND COLUMN_NAME='{2}' ", DataBase.EscapeSqlCharacter(foundRows[i]["TABLE_SCHEMA"].ToString(), false), DataBase.EscapeSqlCharacter(foundRows[i]["TABLE_NAME"].ToString(), false), DataBase.EscapeSqlCharacter(foundRows[i]["COLUMN_NAME"].ToString(), false));
                DataView dvPrimary = new DataView(this.constrainsDataTable, primaryIdentifierfilter, "ORDINAL_POSITION", DataViewRowState.CurrentRows);

                string settingfilter = string.Format("TABLE_SCHEMA='{0}' AND TABLE_NAME='{1}' AND COLUMN_NAME='{2}'", DataBase.EscapeSqlCharacter(foundRows[i]["TABLE_SCHEMA"].ToString(), false), DataBase.EscapeSqlCharacter(foundRows[i]["TABLE_NAME"].ToString(), false), DataBase.EscapeSqlCharacter(foundRows[i]["COLUMN_NAME"].ToString(), false));
                DataRow[] settingfoundRows = this.SettingsDataTable.Select(settingfilter);

                bool IsPrimaryKey = (dvPrimary.Count > 0);
                bool isIdentity = (foundRows[i]["IS_IDENTITY"].ToString() == "1");
                bool isComputed = (foundRows[i]["IS_COMPUTED"].ToString() == "1");
                bool UseGetDateInInsert = Regex.IsMatch(foundRows[i]["COLUMN_DEFAULT"].ToString(), "getdate", RegexOptions.IgnoreCase);
                bool UseGetDateInUpdate = (settingfoundRows.Length > 0 && Convert.ToBoolean(settingfoundRows[0]["UseGetDate"]) == true);
                bool isTimeStamp = dataType.ToLower().Contains("timestamp");


                if (IsPrimaryKey)
                {
                    PrimaryKeyProperty = CsProperty;
                }


                if (isIdentity)
                {
                    PDirectionInsert = "ParameterDirection.InputOutput";
                    PDirectionUpdate = "ParameterDirection.InputOutput";
                    PDirectionInsertUpdate = "ParameterDirection.InputOutput";

                    outputParameterInsertProperty = CsProperty;
                    outputParameterUpdateProperty = CsProperty;
                    outputParameterInsertUpdateProperty = CsProperty;

                }
                if (isComputed)
                {
                    PDirectionInsert = "ParameterDirection.InputOutput";
                    PDirectionUpdate = "ParameterDirection.InputOutput";
                    PDirectionInsertUpdate = "ParameterDirection.InputOutput";

                    outputParameterInsertProperty = CsProperty;
                    outputParameterUpdateProperty = CsProperty;
                    outputParameterInsertUpdateProperty = CsProperty;

                }
                if (UseGetDateInInsert)
                {
                    PDirectionInsert = "ParameterDirection.InputOutput";
                    PDirectionUpdate = "ParameterDirection.InputOutput";
                    PDirectionInsertUpdate = "ParameterDirection.InputOutput";

                    outputParameterInsertProperty = CsProperty;
                    outputParameterInsertUpdateProperty = CsProperty;
                    getDateInInsert = CsProperty;

                }
                if (UseGetDateInUpdate)
                {
                    PDirectionUpdate = "ParameterDirection.InputOutput";
                    PDirectionInsertUpdate = "ParameterDirection.InputOutput";

                    outputParameterUpdateProperty = CsProperty;
                    outputParameterInsertUpdateProperty = CsProperty;
                    getDateInUpdate = CsProperty;

                }
                if (isTimeStamp)
                {
                    PDirectionInsert = "ParameterDirection.InputOutput";
                    PDirectionUpdate = "ParameterDirection.InputOutput";
                    PDirectionInsertUpdate = "ParameterDirection.InputOutput";

                    outputParameterInsertProperty = CsProperty;
                    outputParameterUpdateProperty = CsProperty;
                    outputParameterInsertUpdateProperty = CsProperty;
                    timeStampProperty = CsProperty;

                }


                string columnDeafultvalue = foundRows[i]["COLUMN_DEFAULT"].ToString();
                if (columnDeafultvalue.Trim() != "")
                {
                    if (UseGetDateInInsert || UseGetDateInUpdate)
                    {
                        columnDeafultvalue = "DateTime.Now";
                    }
                    else
                    {
                        while (columnDeafultvalue.StartsWith("(") && columnDeafultvalue.EndsWith(")"))
                        {
                            columnDeafultvalue = Regex.Replace(columnDeafultvalue, @"(^\()|(\)$)", "");
                        }
                        columnDeafultvalue = Regex.Replace(columnDeafultvalue, @"(^"")|(""$)|(^')|('$)", "");

                        if (CsharpType == "string")
                        {
                            columnDeafultvalue = "\"" + columnDeafultvalue + "\"";
                            Operator = "LIKE";
                        }
                        if (CsharpType == "bool" || CsharpType == "bool?")
                        {
                            if (columnDeafultvalue == "1")
                            {
                                columnDeafultvalue = "true";
                            }
                            else
                            {
                                columnDeafultvalue = "false";
                            }
                        }
                        if (CsharpType == "Guid" || CsharpType == "Guid?")
                        {
                            if (columnDeafultvalue == "newid()")
                            {
                                columnDeafultvalue = " Guid.NewGuid()";
                            }
                        }
                    }
                }
                //_ForRelatedobjects_
                if (OptionalKeywords.Contains("_ForRelatedObjects_".ToLower()))
                {
                    string RefernceFilter = string.Format("(ReferenceSchema='{0}') AND (ReferenceTable='{1}') AND (ReferenceColumn='{2}')", DataBase.EscapeSqlCharacter(foundRows[i]["TABLE_SCHEMA"].ToString(), false), DataBase.EscapeSqlCharacter(foundRows[i]["TABLE_Name"].ToString(), false), DataBase.EscapeSqlCharacter(columnName, false));
                    DataView Referncedv = new DataView(this.constrainsDataTable, RefernceFilter, "TABLE_SCHEMA,Table_Name,Column_name", DataViewRowState.CurrentRows);

                    string newTemplate = "";
                    for (int j = 0; j < Referncedv.Count; j++)
                    {
                        string subTemplate = template;
                        string OtherCsClass = this.getCsClass(Referncedv[j]["Table_name"].ToString());
                        string OtherBLClass = this.textBoxBusinessLayerSuffix.Text.Replace("#TABLENAME#", OtherCsClass);

                        subTemplate = Regex.Replace(subTemplate, "_OtherCsClass_", OtherCsClass, RegexOptions.IgnoreCase);
                        subTemplate = Regex.Replace(subTemplate, "_OtherBLClass_", OtherBLClass, RegexOptions.IgnoreCase);
                        subTemplate = Regex.Replace(subTemplate, "_OtherCsProperty_", this.getCsProperty(Referncedv[j]["Column_Name"].ToString()), RegexOptions.IgnoreCase);
                        subTemplate = Regex.Replace(subTemplate, "_CsProperty_", CsProperty, RegexOptions.IgnoreCase);
                        if (subTemplate.ToLower() != template.ToLower())
                        {
                            newTemplate += subTemplate;
                        }
                    }
                    template = newTemplate;
                }

                //_ForRelatedCollections_
                if (OptionalKeywords.Contains("_ForRelatedCollections_".ToLower()))
                {
                    string RefernceToMeFilter = string.Format("(TABLE_SCHEMA='{0}') AND (TABLE_NAME='{1}') AND (COLUMN_NAME='{2}') AND (ReferenceSchema IS NOT NULL) AND (ReferenceTable IS NOT NULL) AND (ReferenceColumn IS NOT NULL)", DataBase.EscapeSqlCharacter(foundRows[i]["TABLE_SCHEMA"].ToString(), false), DataBase.EscapeSqlCharacter(foundRows[i]["TABLE_Name"].ToString(), false), DataBase.EscapeSqlCharacter(columnName, false));
                    DataView RefernceTodv = new DataView(this.constrainsDataTable, RefernceToMeFilter, "ReferenceSchema,ReferenceTable,ReferenceColumn", DataViewRowState.CurrentRows);
                    string newTemplate = "";
                    for (int j = 0; j < RefernceTodv.Count; j++)
                    {
                        string subTemplate = template;
                        string OtherCsClass = this.getCsClass(RefernceTodv[j]["ReferenceTable"].ToString());
                        string OtherBLClass = this.textBoxBusinessLayerSuffix.Text.Replace("#TABLENAME#", OtherCsClass);

                        subTemplate = Regex.Replace(subTemplate, "_OtherCsClass_", OtherCsClass, RegexOptions.IgnoreCase);
                        subTemplate = Regex.Replace(subTemplate, "_OtherBLClass_", OtherBLClass, RegexOptions.IgnoreCase);
                        subTemplate = Regex.Replace(subTemplate, "_OtherCsProperty_", this.getCsProperty(RefernceTodv[j]["ReferenceColumn"].ToString()), RegexOptions.IgnoreCase);
                        subTemplate = Regex.Replace(subTemplate, "_CsProperty_", CsProperty, RegexOptions.IgnoreCase);
                        if (subTemplate.ToLower() != template.ToLower())
                        {
                            newTemplate += subTemplate;
                        }
                    }
                    template = newTemplate;
                }
                

                if (columnDeafultvalue != "" && OptionalKeywords.Contains("!_CsharpDefaultValue_".ToLower()))
                {
                    template = "";
                }
                else if (columnDeafultvalue == "" && OptionalKeywords.Contains("_CsharpDefaultValue_".ToLower()))
                {
                    template = "";
                }
                else if (hasOutputParameterInsert != "" && OptionalKeywords.Contains("!_HasOutputParameterInsert_".ToLower()))
                {
                    template = "";
                }
                else if (hasOutputParameterInsert == "" && OptionalKeywords.Contains("_HasOutputParameterInsert_".ToLower()))
                {
                    template = "";
                }
                else if (hasOutputParameterUpdate != "" && OptionalKeywords.Contains("!_HasOutputParameterUpdate_".ToLower()))
                {
                    template = "";
                }
                else if (hasOutputParameterUpdate == "" && OptionalKeywords.Contains("_HasOutputParameterUpdate_".ToLower()))
                {
                    template = "";
                }
                else if (hasOutputParameterInsertUpdate != "" && OptionalKeywords.Contains("!_HasOutputParameterInsertUpdate_".ToLower()))
                {
                    template = "";
                }
                else if (hasOutputParameterInsertUpdate == "" && OptionalKeywords.Contains("_HasOutputParameterInsertUpdate_".ToLower()))
                {
                    template = "";
                }
                else if (hasTimeStamp != "" && OptionalKeywords.Contains("!_HasTimeStamp_".ToLower()))
                {
                    template = "";
                }
                else if (hasTimeStamp == "" && OptionalKeywords.Contains("_HasTimeStamp_".ToLower()))
                {
                    template = "";
                }
                else if (hasPrimaryKey != "" && OptionalKeywords.Contains("!_HasPrimaryKey_".ToLower()))
                {
                    template = "";
                }
                else if (hasPrimaryKey == "" && OptionalKeywords.Contains("_HasPrimaryKey_".ToLower()))
                {
                    template = "";
                }
                else if (outputParameterInsertProperty != "" && OptionalKeywords.Contains("!_OutputParameterInsertProperty_".ToLower()))
                {
                    template = "";
                }
                else if (outputParameterInsertProperty == "" && OptionalKeywords.Contains("_OutputParameterInsertProperty_".ToLower()))
                {
                    template = "";
                }
                else if (outputParameterUpdateProperty != "" && OptionalKeywords.Contains("!_OutputParameterUpdateProperty_".ToLower()))
                {
                    template = "";
                }
                else if (outputParameterUpdateProperty == "" && OptionalKeywords.Contains("_OutputParameterUpdateProperty_".ToLower()))
                {
                    template = "";
                }
                else if (outputParameterInsertUpdateProperty != "" && OptionalKeywords.Contains("!_OutputParameterInsertUpdateProperty_".ToLower()))
                {
                    template = "";
                }
                else if (outputParameterInsertUpdateProperty == "" && OptionalKeywords.Contains("_OutputParameterInsertUpdateProperty_".ToLower()))
                {
                    template = "";
                }
                else if (timeStampProperty != "" && OptionalKeywords.Contains("!_TimeStampProperty_".ToLower()))
                {
                    template = "";
                }
                else if (timeStampProperty == "" && OptionalKeywords.Contains("_TimeStampProperty_".ToLower()))
                {
                    template = "";
                }
                else if (PrimaryKeyProperty != "" && OptionalKeywords.Contains("!_PrimaryKeyProperty_".ToLower()))
                {
                    template = "";
                }
                else if (PrimaryKeyProperty == "" && OptionalKeywords.Contains("_PrimaryKeyProperty_".ToLower()))
                {
                    template = "";
                }
                else if (getDateInInsert != "" && OptionalKeywords.Contains("!_GetDateInInsert_".ToLower()))
                {
                    template = "";
                }
                else if (getDateInInsert == "" && OptionalKeywords.Contains("_GetDateInInsert_".ToLower()))
                {
                    template = "";
                }
                else if (getDateInUpdate != "" && OptionalKeywords.Contains("!_GetDateInUpdate_".ToLower()))
                {
                    template = "";
                }
                else if (getDateInUpdate == "" && OptionalKeywords.Contains("_GetDateInUpdate_".ToLower()))
                {
                    template = "";
                }

                else if (onlyOne == "" && OptionalKeywords.Contains("_OnlyOne_".ToLower()))
                {
                    template = "";
                }
                else
                {
                    template = Regex.Replace(template, "_Empty_", "", RegexOptions.IgnoreCase);
                    template = Regex.Replace(template, "_OtherEmpty_", "", RegexOptions.IgnoreCase);
                    template = Regex.Replace(template, "_ColumnName_", columnName, RegexOptions.IgnoreCase);
                    template = Regex.Replace(template, "_CsharpType_", CsharpType, RegexOptions.IgnoreCase);
                    template = Regex.Replace(template, "_CsharpTypePure_", CsharpType.Replace("?", ""), RegexOptions.IgnoreCase);
                    template = Regex.Replace(template, "_CsField_", CsField, RegexOptions.IgnoreCase);
                    template = Regex.Replace(template, "_CsProperty_", CsProperty, RegexOptions.IgnoreCase);
                    template = Regex.Replace(template, "_CsharpDefaultValue_", columnDeafultvalue, RegexOptions.IgnoreCase);

                    template = Regex.Replace(template, "_ParameterName_", PName, RegexOptions.IgnoreCase);
                    template = Regex.Replace(template, "_parameterNumber_", parameterNumber, RegexOptions.IgnoreCase);

                    template = Regex.Replace(template, "_ParameterDirection_", PDirection, RegexOptions.IgnoreCase);
                    template = Regex.Replace(template, "_ParameterDirectionInsert_", PDirectionInsert, RegexOptions.IgnoreCase);
                    template = Regex.Replace(template, "_ParameterDirectionUpdate_", PDirectionUpdate, RegexOptions.IgnoreCase);
                    template = Regex.Replace(template, "_ParameterDirectionInsertUpdate_", PDirectionInsertUpdate, RegexOptions.IgnoreCase);

                    template = Regex.Replace(template, "_ColumnOrder_", columnOrder, RegexOptions.IgnoreCase);
                    template = Regex.Replace(template, "_DbType_", DbType, RegexOptions.IgnoreCase);
                    template = Regex.Replace(template, "_IsPrimaryKey_", IsPrimaryKey.ToString(), RegexOptions.IgnoreCase);
                    template = Regex.Replace(template, "_IsIdentity_", isIdentity.ToString(), RegexOptions.IgnoreCase);
                    template = Regex.Replace(template, "_IsComputed_", isComputed.ToString(), RegexOptions.IgnoreCase);
                    template = Regex.Replace(template, "_IsTimeStamp_", isTimeStamp.ToString(), RegexOptions.IgnoreCase);
                    template = Regex.Replace(template, "_Operator_", Operator, RegexOptions.IgnoreCase);
                }
                s += template;
            }
            s = s.Trim();
            if (s.EndsWith(","))
            {
                s = s.Substring(0, s.Length - 1);
            }
            return s;
        }
        #endregion

    }
}